Can I remove a reference to a specific cell in a rule containing a formula so I can reuse it across-CodePudding

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()

CodePudding user response：

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