Variable losing set Range

[kmowers07]

I am using VBA to define a Range by Searching the sheet for the specific column header name and then using that in conditional formatting formula.
The reason is we add columns all the time and i want to make this conditional formatting dynamic.
the Issue i am getting is the Error: Object Variable or with block variable not set when it uses it in the conditional formatting.
I hover over the defined range and it equals = Nothing when the column is there in the roster.
Now i have deleted the name of the column retyped it and copied it and pasted right back into the VBA and it works but i save and close the roster and it does it again

Sometimes it finds the name of the column header and other times it can't, i am not sure why it can't find it all the time.
Any assistance would be appreciated.

Sub Color2()
Dim wb As Workbook
    Dim ws As Worksheet
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Roster")
   'defining the Range of the workbook
        Dim lastrow As Long
        lastrow = Range("A" & Rows.Count).End(xlUp).Row
        
'Prep to clear out the data and find the first blank column in the report table.
Dim FirstBlankColumn As Range
    'Find the first cell in row 1 that is blank.
    Set FirstBlankColumn = ws.Range("A1:XFD1").Find("", LookAt:=xlWhole)

'Create a variable to find the column letter we are clearing.
Dim ColumnLetter As String

'Get the letter of the cell one to left of the blank cell we found.
ColumnLetter = Split(FirstBlankColumn.Offset(0, -1).Address, "$")(1)


    

        Dim wrksht As Range
        Set wrksht = ws.Range("A2:" & ColumnLetter & lastrow)

'delete all conditional formatting
ws.Cells.FormatConditions.Delete


'defines the variable as range
Dim TermInfoColumn As Range
'finds the Term Info Column in the Roster
    Set TermInfoColumn = ws.Range("A1:XFD1").Find("Term Info", LookAt:=xlWhole)


'if V2 equals non the row will be yellow and bold
wrksht.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & TermInfoColumn.Offset(1, 0).Address(RowAbsolute:=False) & " = ""Non"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Application.CutCopyMode = False


'if V2 equals FL row will be Peach color font black, bold, italic
    wrksht.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & TermInfoColumn.Offset(1, 0).Address(RowAbsolute:=False) & " =""FL"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = True
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .Color = RGB(244, 176, 132)
        
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub

 

[MARK858]

What column header is the first blank when the code doesn't find it?

 

[kmowers07]

Its looking for the last blank column and the last blank row.
That variable is not the issue.
The issue is with the "TermInfoColumn" Variable

   Set TermInfoColumn = ws.Range("A1:XFD1").Find("Term Info", LookAt:=xlWhole)

when i hover over it it equals "Nothing"

 

[MARK858]

Same Question just what cell is "Term Info" in when it says "Nothing"

Btw

Set FirstBlankColumn = ws.Range("A1:XFD1").Find("", LookAt:=xlWhole)

is looking for what it was previously set as, either the first or last blank cell/Row/Column not necessarily the last as you haven't specified the search direction

 

[kmowers07]

Currently it is Column AE1 for Term info and the currentblank column is EG1
I just looked up the syntax for Range.Find Method and will add more information to the search and see if that helps as well.

 

[MARK858]

The reason I asked was because it can skip A1 the way the code is written with FindNext but that is not in this case.
What does the code below

Set TermInfoColumn = ws.Range("A1:XFD1").Find("Term Info", LookAt:=xlPart)
Msgbox TermInfoColumn.Address

return if there are no other cells containing "Term Info" before EG1

and if it finds EG1 what does the formula

=LEN(EG1)

return?

 

[Akuini]

Sometimes it finds the name of the column header and other times it can't, i am not sure why it can't find it all the time.

See if this discussion helps:
trouble-with-find

 

[kmowers07]

The reason I asked was because it can skip A1 the way the code is written with FindNext but that is not in this case.
What does the code below

Set TermInfoColumn = ws.Range("A1:XFD1").Find("Term Info", LookAt:=xlPart)
Msgbox TermInfoColumn.Address

return if there are no other cells containing "Term Info" before EG1

and if it finds EG1 what does the formula

=LEN(EG1)

return?

Changing the code to xlPart, it returns TermInfoColumn = Nothing, it still returns the same error message as before Object variable or with block variable not set

 

[MARK858]

What does the formula give you?