How to set value in the dropdown from a JSON data list?

Clear cell color if cell contain value

• 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