Have formula in VBA be based on offsets?

[Knockoutpie]

Is there any way to re-write this formula to basically say instead of A5, Offset -2 rows from "brand"

The goal here is to have the macro find the cell with text "Brand" in row 3, once found, offset down 2 cells and apply conditonal formatting to the range of data for that column (not the first 4 rows)

    Rows(3).Find("Brand", LookIn:=xlValues, lookat:=xlWhole).Offset(-2)

Cells.Find(What:="EndCustName").Activate

ActiveCell.Offset(-2, 0).Select

ActiveCell.Value = "Import"


    Range("A5").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=ISERROR(XLOOKUP($A5,Brands!$A:$A,Brands!$A:$A))"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Interior

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = False

 

[rlv01]

If should be possible base things on an offset. One example.

    Dim rngFound As Range
    Set rngFound = Rows(3).Find("Brand", LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFound Is Nothing Then
        'Found "Brand"
        With rngFound.Offset(2)  '+2 is down, -2 is up
            '
            ' your code here
            '
        End With
    Else
        'Did not find "Brand"
        MsgBox "'Brand' not found" 'msgbox optional
    End If

 

[Knockoutpie]

If should be possible base things on an offset. One example.

    Dim rngFound As Range
    Set rngFound = Rows(3).Find("Brand", LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFound Is Nothing Then
        'Found "Brand"
        With rngFound.Offset(2)  '+2 is down, -2 is up
            '
            ' your code here
            '
        End With
    Else
        'Did not find "Brand"
        MsgBox "'Brand' not found" 'msgbox optional
    End If

That part I understand, this part I don't.

Basically, conditional formatting is applying a formula to lookup the values from a 2nd sheet "brand" if the value is not found, it highlights the unfound cell in red (because it's spelled incorrectly)

The formula is set to start on A5 (lookup value), but I'd like it to be 2 cells below where "brand" is found.

"=ISERROR(XLOOKUP($A5,Brands!$A:$A,Brands!$A:$A))"

you see here the formula for conditional formatting is set on A5, but is there a way to have it as "active cell" or something like that?

 

[rlv01]

There is nothing to prevent you from using VBA to build a formula with an offset built in, but if all you want to do is highlight cells that the XLOOKUP function does not return a match for, there are easier ways that complicated offset formulas. Instead take advantage of the [if_not_found] optional parameter to establish a standard not found return value and base your conditional formatting off that.