VBA to recognise cells with drop lists

[didijaba]

hi, how could I check sheet for cells that have drop lists and change such cells fill to red?thx

 

[njimack]

Sub Validation()

Dim c As Range


Application.ScreenUpdating = False


For Each c In ActiveSheet.UsedRange
    If c.Validation.InCellDropdown = True Then c.Interior.Color = 255
Next c


Application.ScreenUpdating = True


End Sub

 

[didijaba]

thanks a lot njimack

 

[njimack]

You're welcome. Just thought of a much quicker way, but this would apply the formatting to any cell with validation (not just drop-downs) - this might be what you wanted, or it might not...

Sub Validation2()

ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation).Interior.Color = 255


End Sub

 

[didijaba]

Thanks, second is better.