Inserting an xlookup in VBA code to format a cell based on it's info in another table / sheet.

[crzytimes]

Hi - I'm trying to reduce the number of "helper" columns I have in a table as it's gotten too big. My helper column is just using an xlookup to pull the data into the helper column. I use the helper column to conditionally format a row or cells. I'm doing something wrong, and can't quite figure it out.

Below is the original VBA Code, what I want to do is replace "=PlanSched!$O2=""TECO""" with a formula that says "=xlookup($G2,COOISPI[Order],COOISPI[Status])=""TECO""". Where COOISPI is a table in another worksheet labeled "SAP_Export".

Original VBA Code

' Date Color Code
    Sheets("PlanSched").Range("$C2", Range("$C2").End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=PlanSched!$O2=""TECO"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Strikethrough = False
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Selection.FormatConditions(1).StopIfTrue = False