In Conditional Data Validation, I described how to set up conditional formatting to alert you when your list validation was no longer valid. I also noticed that this is covered in Professional Excel Development (and much better, I might add) so if you haven’t ordered your copy yet, I suggest you do.
I recently had cause to use this technique, but my lists were all over the place. I couldn’t set up the CF on one cell, then copy it to the others because the relationship between the cells wasn’t the same as the relationship between the lists. I created this macro to loop through all the cells on the sheet and if there’s list validation, it adds conditional formatting.
Sub FormatValidation()
Dim rCell As Range
Dim lValType As Long
Dim CllFc As FormatCondition
For Each rCell In Sheet1.UsedRange.Cells
‘set valtype to a number that is not valid for the enumeration
lValType = -1
‘Attempt to read the cell’s validation type
On Error Resume Next
lValType = rCell.Validation.Type
On Error GoTo 0
‘If the cell had validation and the type was ‘list’
If lValType = xlValidateList Then
‘Delete existing conditional formatting
On Error Resume Next
rCell.FormatConditions.Delete
On Error GoTo 0
‘Create format condition with a formula that looks like:
‘=ISERROR(MATCH(cell, validationlistrange, FALSE))
With rCell.Validation
Set CllFc = rCell.FormatConditions.Add(Type:=xlExpression, _
Formula1:=“=ISERROR(MATCH(“ & rCell.Address & “,” & _
Right(.Formula1, Len(.Formula1) – 1) & “,FALSE))”)
End With
CllFc.Interior.Color = vbRed
End If
Next rCell
End Sub
Dim rCell As Range
Dim lValType As Long
Dim CllFc As FormatCondition
For Each rCell In Sheet1.UsedRange.Cells
‘set valtype to a number that is not valid for the enumeration
lValType = -1
‘Attempt to read the cell’s validation type
On Error Resume Next
lValType = rCell.Validation.Type
On Error GoTo 0
‘If the cell had validation and the type was ‘list’
If lValType = xlValidateList Then
‘Delete existing conditional formatting
On Error Resume Next
rCell.FormatConditions.Delete
On Error GoTo 0
‘Create format condition with a formula that looks like:
‘=ISERROR(MATCH(cell, validationlistrange, FALSE))
With rCell.Validation
Set CllFc = rCell.FormatConditions.Add(Type:=xlExpression, _
Formula1:=“=ISERROR(MATCH(“ & rCell.Address & “,” & _
Right(.Formula1, Len(.Formula1) – 1) & “,FALSE))”)
End With
CllFc.Interior.Color = vbRed
End If
Next rCell
End Sub
