VBA DropDown search

[GeorgesT]

Hello MVP's and other VBA pro's,

I have a problem in VBA.
I want to do a look up in a column containing dropdowns in a range (E:17, G59).
The drop down has two options, 1 is containing only blanks (from a hidden sheet, linked to the input of column A) the other is containing any text value (from the same hidden sheet).
If the dropdown contains only blanks it should do nothing. If it contains any text value, it should highlight red.

Is there a way I can apply this event in the worksheet?

Kind regards
Georges

 

[GeorgesT]

I came up with this event, but it gives an error "Unable to get the specialcellsproperty of the range class"



Private Sub Worksheet_Change(ByVal Target As Range)

For Each x In Columns(5).SpecialCells(xlCellTypeAllFormatConditions, xlDropDown)
If x.Value = True Then
Cells(k, 5).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.749992370372631
.PatternTintAndShade = 0
End With
MsgBox "ok"
End If
Next x
On Error Resume Next
End Sub