Knockoutpie
Board Regular
- Joined
- Sep 10, 2018
- Messages
- 116
- Office Version
- 365
- Platform
- Windows
Hi All,
is there a way to adjust this to that the formula uses the active cell instead of G5?
The reason i'm doing this is because the brand column, or first cell under brand column, will always be in a different place.
I'm applying a conditional formatting rule so that if a brand is not found on the brand sheet in column A, it will highlight the cell which isn't found.
is there a way to adjust this to that the formula uses the active cell instead of G5?
The reason i'm doing this is because the brand column, or first cell under brand column, will always be in a different place.
I'm applying a conditional formatting rule so that if a brand is not found on the brand sheet in column A, it will highlight the cell which isn't found.
VBA Code:
' Select column based on "brand"
Columns("G:G").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Brands!A:A,G5)=0" ' Can I change G5 to be active cell?
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False