Conditional formatting based on value with offset

Question

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.

Answer 1

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").