Conditional Formatting using UDF not setting using VBA

[KASims]

I have a UDF that highlights the cell header if the column is filtered:

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:

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?

 

[6StringJazzer]

When asking about an error always give the error number and error description.

 

[KASims]

When asking about an error always give the error number and error description.

It does not actually throw an error. It just aborts execution. I have an On Error statement added but it isn't triggered. That is what makes this so confusing.

On Error GoTo Error_Exit

    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
    
Error_Exit:

    If Not Error Is Nothing Then
        MsgBox "Error in Macro2" & vbNewLine & vbNewLine & Error, vbOKOnly, "ERROR"
    End If
End Sub

 

[6StringJazzer]

The correct object is Err, not Error. If you were declaring variables this would have shown up at compile time. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

The following is the preferred technique for error handling.

On Error GoTo Error_Exit

    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
   
    Exit Sub

Error_Exit:

    MsgBox "Error in Macro2" & vbNewLine & vbNewLine & Err.Number & ": " & Err.Desc, vbOKOnly, "ERROR"

End Sub

 

[KASims]

uh, dumb me, sorry about that.

However, even with fixing that, the error trap is never executed. It just stops at the Format.Conditions.Add statement.
I have updated my code as follows, but with no change in results
.

Function Is_Col_Filtered(Header As Range) As Boolean

    Dim wsh As Worksheet
   
    Set wsh = Header.Parent
    Is_Col_Filtered = wsh.AutoFilter.Filters(Header.Column).On
   
End Function
   

Sub Macro3()
   
    Dim ws_this As Worksheet
    Dim rng_this As Range
    Dim Msg As String
   
    On Error GoTo Error_Exit
   
        Set ws_this = ThisWorkbook.Worksheets("2022.07.18")
        Set rng_this = ws_this.Range("A1:O1")
      
        With rng_this
            .Select
            .FormatConditions.Add Type:=xlExpression, Formula1:="=Is_Col_Filtered(A1)"
            .FormatConditions(rng_this.FormatConditions.Count).SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 49407
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
   
Error_Exit:
   
    If Err.Number <> 0 Then
        Msg = "Error # " & str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) _
         & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
     End If
        
     Set ws_this = Nothing
     Set rng_this = Nothing
        
End Sub

 

[6StringJazzer]

Hmm. I'm stuck at this point. When code halts execution without raising an error it's generally something really bizarre, because that's not supposed to happen. Hard to troubleshoot without having the file.

Have you tried stepping through the code using F8 in the debugger to see if it also halts that way?