Can I remove a reference to a specific cell in a rule containing a formula so I can reuse it across multiple columns?

Question

I'm trying to apply the same rule to different columns, which will fill the cell with the color green if it is empty.

I'm getting the rule through recording a Macro, but cannot figure out a successful way of removing the reference to range B2 below.

I would like to have it as a rule I can apply to multiple selected columns.

Is there anything I can substitute in?

Public Sub FillGreenIfCellNotEmpty()
    selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(B2))>0"
    selection.FormatConditions(selection.FormatConditions.count).SetFirstPriority
    With selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    selection.FormatConditions(1).StopIfTrue = False

End sub

I have tried substituting "cells(1,1)" instead of B2 to reference the first cell of the selection and also substituting "selection".

Currently, I don't fully understand how rules work with instant updating. I would have thought the formula would be more along the lines of if not isempty(selection) rather than LEN() and TRIM()

Answer 1

Try this please:

Option Explicit

Public Sub FillGreenIfCellNotEmpty()
Dim x As String
x = Selection.Address(0, 0) 

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(" & x & " ))>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

End Sub