I have a UDF that highlights the cell header if the column is filtered:
To be sure I had the coding correct, I manually added it in Excel directly and recorded the following macro:
The Conditional formatting works correctly when I add it manually, but the macro does not add the conditional formatting formula. Debugging, it aborts on the Add statement.
Can someone please help me understand what is wrong?
VBA Code:
Function Is_Col_Filtered(Header As Range) As Boolean
Dim wsh As Worksheet
Set wsh = ActiveSheet
Is_Col_Filtered = wsh.AutoFilter.Filters(Header.Column).On
End Function
To be sure I had the coding correct, I manually added it in Excel directly and recorded the following macro:
VBA Code:
Sub Macro2()
Range("A1:O1").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=Is_Col_Filtered(A1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
The Conditional formatting works correctly when I add it manually, but the macro does not add the conditional formatting formula. Debugging, it aborts on the Add statement.
Can someone please help me understand what is wrong?