VBA formula to use active cell instead of "G5" / "=COUNTIF(Brands!A:A,G5)=0"

[Knockoutpie]

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.

    ' 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

 

[Knockoutpie]

I will use the following to select the data in which conditional formatting will be applied.

This will search for the word "brand" in row 3, go down 2 rows, and select that cell and the data to the bottom of the worksheet.

However this still does not resolve on how I can have a formula use an active cell instead of a specified cell.

' Find Brand
Dim a1
Dim b2 As Range
For Each a1 In Split("Brand", "|")
    Set b2 = ActiveSheet.Rows(3).Find(a1, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not b2 Is Nothing Then
        b2.Offset(2).Select
        Exit For
    End If
Next
   Range(Selection, Selection.End(xlDown)).Select
    Selection.Select