Defining a Range based on a Named Range from another Sheet?

[jmpatrick]

Happy Friday!

I'm trying to define run code only if the value does not match one of the values in a Named Range on another sheet. Sadly, it's not working.

Here's what I have:

Sub AddJobEditHyperlinks()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim cl As Range
    Dim Hol As Range
    
    Set Hol = Worksheets("Holidays").Range("HolidaysAll")
    
    For Each cl In Range("SubLotColumn", Range("C" & Rows.Count).End(xlUp))
        
        If cl.Value <> Hol Then
            ActiveSheet.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:=cl.Address, ScreenTip:="Click To Open Job Edit Window"
        End If
        
    Next cl
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[Joe4]

You are welcome!

Yes, instead of nesting a second loop inside the first, I am simply using the COUNTIF function to count the number of times the value is found in the HolidaysAll list.
If that count is 0, it means the value is not found.

It is usually preferrable to try to limit the number of loops used in VBA code, as they are notoriously slow and inefficient.