Clear cell color if cell contain value

Question

How are you? I would like to ask for your help inVBA. I have two columns; D and E. Column D contains List of Objects while Column E is used to classify each object where data is selected from a dropdown list (Blue, Red, Green).

I've written a short macro to highlight any blank cells found in column E in grey color, and it worked fine for now. However, I realized that after I filled up the blank cells, the cells color still remained.

This is what I would like to do: 1. Clear cell color of the blank cells in Column E as I select data from the dropdown list (any value).

This is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range) Set sh = ThisWorkbook.Worksheets("Audit Findings") 'I don't know how to set the target for range in column E If Target = sh.Range("E20", sh.Cells(1001, "E").End(xlUp)) Then If Target.Value <> "" Then sh.Range("E20", sh.Cells(1001, "E").End(xlUp)).Interior.ColorIndex = xlNone End If End If End Sub

My problem is that I do not know how to define the target for column E (starting row is 20) as it also refers to the last cell that contains object in column D.

Please help.

Answer

Jay,

I believe this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range) If target.count > 1 then exit sub If Target.row > 20 and target.column = 5 then

If Target.Value <> "" Then
  Target.Interior.ColorIndex = xlNone
End If

End If End Sub

An alternate approach would be to clear all the backgrounds in column E beyond row 20, then loop through and gray the empty cells

Private Sub Worksheet_Change(ByVal Target As Range) Dim r as Range, cell as Range

If Target.row > 20 and target.column = 5 then

set r = me.Range("E20",me.cells(me.rows.count,"D").End(xlup).offset(0,1))
r.Interior.ColorIndex = xlNone
for each cell in r
  if len(trim(cell.text)) = 0 then
     cell.Interior.ColorIndex = 15  'change to your gray colorindex
  end if
Next

End if End Sub