Conditional formatting based on value with offset

0 votes

I need some assistance with the coding to fix my problem. Having trouble knowing where to begin. What I'm trying to achieve is to colour a specific range (using offset) if a value in a cell equals a name in a list.

Because I have to do it numerous times, I'd prefer to utilise conditional formatting in Vba (it is a big sheet)

Example: "Juliet" is the value in cell B5. In column K, I have a list of names, and in column L, I have a list of colours. If the word "juliet" appears on the list and is highlighted in the colour indicated next to it, I want to conditionally format the range B3 to B6.

I need to repeat itself, så the placement of "Juliet" could be anywhere, but always color the name, 2 cells above and 1 cell below.

Hope someone can help me get started the right way.

Example

Apr 4, 2023 in Others by narikkadan
• 63,420 points
374 views

1 answer to this question.

0 votes

Apart than the altered cell, Conditional Formatting should not be used to change other cells. Please copy the following code event into the discussion code module's sheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Count > 1 Then Exit Sub 'it works only for A SINGLE CELL CHANGE
   
   If Not Intersect(Target, Me.Range("A:H")) Is Nothing And Target.Row >= 3 Then
        Dim lastR As Long: lastR = Me.Range("K" & Me.Rows.Count).End(xlUp).Row
        Dim rngNam As Range: Set rngNam = Range("K2:K" & lastR)
        Dim f As Range, prevVal As String
        If Target.Value <> "" Then
            Set f = rngNam.Find(what:=Target.Value, LookIn:=xlValues, Lookat:=xlWhole)
            If Not f Is Nothing Then
                Me.Range(Target.Offset(-2), Target.Offset(1)).Interior.ColorIndex = f.Offset(, 1).Interior.ColorIndex
            End If
        Else
          Application.EnableEvents = False
            Application.Undo
            prevVal = Target.Value
            Target.Value = ""
            Set f = rngNam.Find(what:=prevVal, LookIn:=xlValues, Lookat:=xlWhole)
            If Not f Is Nothing Then
                Me.Range(Target.Offset(-2), Target.Offset(1)).Interior.ColorIndex = xlNone
            End If
          Application.EnableEvents = True
        End If
   End If
End Sub

When the name is cleared, the interior of the cell is also cleared.

To open the sheet code module window, right-click on the sheet on which you're trying to insert the appropriate names, select View Code, and then paste the aforementioned code into the resulting window.

Of course, you have to have the names given (in the "K:K" column) and the colour of the interior cells that match in the next one ("L:L").

answered Apr 4, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel Conditional Formatting based on Adjacent Cell Value

The row number used in the formula ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
1,434 views
0 votes
1 answer

VBA Change Cell colors based on value, and it can deal with single cell and multiple cells changes

Before looping through all of the cells ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
442 views
0 votes
1 answer

Conditional format based on external cell value

Use a rule with the formula =$Q6="Yes" and apply ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,420 points
382 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,420 points
1,095 views
0 votes
1 answer

Format an Excel Sheet in Python

The below code will help you refer ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,420 points
1,108 views
0 votes
1 answer
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
910 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,231 views
0 votes
1 answer

Struggling to move object based on the value to respect sheet

If you are not moving many rows ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
320 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP