Using named ranges with an application formula to hide columns

Question

I want to hide (unhide) a couple of columns on an Excel spreadsheet with VBA code using an application formula with named ranges. The following code will not work, but gives a gist of what I want to accomplish.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    IF(Range("C1").Value = 1 and Application.WorksheetFunction.COUNTIF(aa_1ltr,AA_Sequence)=0) OR Application.WorksheetFunction.COUNTIF(aa_3ltr,AA_Sequence)=0) Then
        Columns("I").EntireColumn.Hidden = False
        Columns("J").EntireColumn.Hidden = False
    Else
        Columns("I").EntireColumn.Hidden = True
        Columns("J").EntireColumn.Hidden = True
    End If
End Sub

It will not work as it need an evaluation statement somewhere. But more importantly, I need the correct syntax to use the application formula COUNTIF that has named ranges. FYI, C1 can only be the integers 1 or 3.

This code is failing with the Set statements and I do not know why.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aa_1ltr As Range
    Dim aa_3ltr As Range
    Dim AA_Sequence As Range
    Dim Boolie As Boolean

    Set aa_1ltr = Me.Range("[" & ThisWorkbook.Name & "]" & Me.Name & "!aa_1ltr")
    Set aa_3ltr = Me.Range("[" & ThisWorkbook.Name & "]" & Me.Name & "!aa_3ltr")
    Set AA_Sequence = Me.Range("[" & ThisWorkbook.Name  & "]" & Me.Name & "!AA_Sequence")

    If (Range("C1").Value = 1 And Application.WorksheetFunction.CountIf(aa_1ltr, AA_Sequence) = 0) Or _
(Range("C1").Value = 3 And Application.WorksheetFunction.CountIf(aa_3ltr, AA_Sequence) = 0)  Then
        Columns("I").EntireColumn.Hidden = True
        Columns("J").EntireColumn.Hidden = True
    Else
        Columns("I").EntireColumn.Hidden = False
        Columns("J").EntireColumn.Hidden = False
    End If

End Sub

Answer 1

This works for me. I'm having trouble figuring out which order you want your logical statements in, so you might need to rearrange them. But it references and tests named ranges correctly:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aa_11tr As Range
    Dim aa_31tr As Range
    Dim AA_Sequence As Range
    Dim Boolie As Boolean

    Set aa_11tr = Me.Range("[" & ThisWorkbook.Name & "]" & Me.Name & "!aa_11tr")
    Set aa_31tr = Me.Range("[" & ThisWorkbook.Name & "]" & Me.Name & "!aa_31tr")
    Set AA_Sequence = Me.Range("[" & ThisWorkbook.Name & "]" & Me.Name & "!AA_Sequence")

    Boolie = _
        Application.WorksheetFunction.CountIf(aa_11tr, AA_Sequence) = 0 Or _
        Application.WorksheetFunction.CountIf(aa_31tr, AA_Sequence) = 0

    If Range("C1").Value = 1 And Boolie Then
        Columns("I").EntireColumn.Hidden = False
        Columns("J").EntireColumn.Hidden = False
    Else
        Columns("I").EntireColumn.Hidden = True
        Columns("J").EntireColumn.Hidden = True
    End If

End Sub

Example: