Knockoutpie
Board Regular
- Joined
- Sep 10, 2018
- Messages
- 116
- Office Version
- 365
- Platform
- Windows
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)
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)
VBA Code:
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