VBA Code - Undo the Highlight Row when Cell is Blank

[Darlie247]

Hi there,

I have a VBA code that is working great for me thus far. I have it set up that if the drop down is selected as Testing, Passed, or Failed, it will highlight the entire row as yellow, green, or red. However, this color formatting will remain for the entire row even if I delete the entry. For example, if I select the cell to say "Testing" by mistake and then I delete this, the entire row will remain as yellow. Is there something I can add to my code so that if the cell is blank, it will change back to "no fill"?

Here is a sample of what my sheet looks like as well as my code:

AGAIN.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2							Validation
3							Testing
4
5
6
7
8							Passed
9
10							Testing
11							Passed
12
13							Testing
14
15							Failed
16
17
18
19
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G3:G19	List	Testing, Passed, Failed

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range, Addr As String
   With Range("G3", Cells(Rows.Count, "G").End(xlUp))
    Set Cell = .Find("*Testing*", , xlValues, , , , False, , False)
    If Not Cell Is Nothing Then
      Addr = Cell.Address
      Do
        Cell.EntireRow.Interior.ColorIndex = 6
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> Addr
    End If
  End With
 
   With Range("G3", Cells(Rows.Count, "G").End(xlUp))
    Set Cell = .Find("*Failed*", , xlValues, , , , False, , False)
    If Not Cell Is Nothing Then
      Addr = Cell.Address
      Do
        Cell.EntireRow.Interior.ColorIndex = 3
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> Addr
    End If
  End With
  
     With Range("G3", Cells(Rows.Count, "G").End(xlUp))
    Set Cell = .Find("*Passed*", , xlValues, , , , False, , False)
    If Not Cell Is Nothing Then
      Addr = Cell.Address
      Do
        Cell.EntireRow.Interior.ColorIndex = 4
        Set Cell = .FindNext(Cell)
      Loop While Not Cell Is Nothing And Cell.Address <> Addr
    End If
  End With
End Sub

 

[igold]

Does something like this work for you...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
        For Each Cell In Range("G3", Cells(Rows.Count, "G").End(xlUp))
            Select Case Cell.Text
                Case Is = "Testing"
                    Cell.EntireRow.Interior.ColorIndex = 6
                Case Is = "Failed"
                    Cell.EntireRow.Interior.ColorIndex = 3
                Case Is = "Passed"
                    Cell.EntireRow.Interior.ColorIndex = 4
                Case Else
                    Cell.EntireRow.Interior.ColorIndex = 0
            End Select
        Next
End Sub

 

[rollis13]

Add these three lines of code at the beginning of your event macro Worksheet_SelectionChange, they will clear all highlighting and re-ably it where needed:

Application.EnableEvents = False
Cells.Interior.Pattern = xlNone
Application.EnableEvents = True

 

[Darlie247]

Does something like this work for you...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
        For Each Cell In Range("G3", Cells(Rows.Count, "G").End(xlUp))
            Select Case Cell.Text
                Case Is = "Testing"
                    Cell.EntireRow.Interior.ColorIndex = 6
                Case Is = "Failed"
                    Cell.EntireRow.Interior.ColorIndex = 3
                Case Is = "Passed"
                    Cell.EntireRow.Interior.ColorIndex = 4
                Case Else
                    Cell.EntireRow.Interior.ColorIndex = 0
            End Select
        Next
End Sub

Thanks for making my code MUCH more simpler!! Works wonderfully

Add these three lines of code at the beginning of your event macro Worksheet_SelectionChange, they will clear all highlighting and re-ably it where needed:

Application.EnableEvents = False
Cells.Interior.Pattern = xlNone
Application.EnableEvents = True

Thanks! This one works as well, and I will definitely be using this for other codes I have

Cheers!

 

[igold]

You're welcome. We were both happy to help. Thanks for the feedback.