Conditional Formatting Validation Macro

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
            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
Posted in Uncategorized

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.