Microsoft Office Forums - View Single Post - Have a cell filled black when blank in dropdown list

NoSparks · 04-05-2017, 06:33 AM

I realize this is not posted under Excel Programming and this will require the file to be a macro enabled .xlsm, but I would remove the conditional formatting and use the Worksheet_Change event as it triggers when a selection is made in a validation drop down.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Or Target.Column <> 2 Then Exit Sub
If Not Intersect(Target, Me.Range("B12:B146")) Is Nothing Then
    If Target.Value = "" Then
        Target.Interior.ColorIndex = 1
    Else
        Target.Interior.ColorIndex = 0
    End If
End If

End Sub