Check for a value in dynamic range, and based on that change the color of columns A, B & C (of same row) to RED

Question

I am trying to see in a dynamic range ( in a column), if the cell value is equal to 0. If yes, then display a pop up message and change the color of column A, B and C (same row number) to RED.

Any help is greatly appreciated.!! Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range(Cells(9, "AD"), _ 
        Cells(Rows.Count, "AD").End(xlUp).Resize(, 1))

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

            If Application.Intersect(KeyCells, Range(Target.Address)) = 0 Then

                MsgBox "Row " & Target.Address & " will not be considered."

            End If

~~ I tried using below code in above IF block, but get the same result of all the rows (till current count) getting a change in background color. I intend to limit color change to just that row and column A, B , C (cells).

~~ Now, here I want to see, if the cell value is equal to '0' then the first 3 column cells (column A, B, C of same row number should be updated with a different background color). ~~ I tried changing the complete row color to RED, but while doing so, all the rows till current row will get a color change. This is not intended. Is this happening because the range is dynamic?

For Each Cell In KeyCells
    Select Case Cell.Value
        Case Is = 0
            Cell.EntireRow.Interior.ColorIndex = 3
        Case Else
            Cell.EntireRow.Interior.ColorIndes = xlNone
    End Select
Next

End If
End Sub

Kazimierz Jawor Kazimierz Jawor · Accepted Answer · 2013-10-03T11:45:19

Your question is a bit unclear however possibly this snippet of code ill help you:

'your code here until...
        If Application.Intersect(KeyCells, Range(Target.Address)) = 0 Then

            MsgBox "Row " & Target.Address & " will not be considered."

        End If

'color for range A:C in target row
Select Case Target.Value
    Case Is = 0
    'into red
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Interior.ColorIndex = 3
    Case Else
    'to none color
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Interior.ColorIndex = xlNone
End Select

'no looping

End if
End sub

Check for a value in dynamic range, and based on that change the color of columns A, B & C (of same row) to RED

1 Answers