Conditional formatting macro for dynamic ranges

Question

Good Day

I need help with a conditional formatting macro.

My goal is to select a couple of cells then press a button to apply conditional formatting to that selection.

It need to change the cell fill colour to red if the cell is blank and change the fill colour and font colour to green if it is not blank.

I would thus end up with a selection of red and green cells if it works correctly.

I have played with the Macro recorder and came up with the following:

Sub testcolor1()

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=LEN(TRIM(G11))=0"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Interior

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = False

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=LEN(TRIM(G11))>0"

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font

        .Color = -11489280

        .TintAndShade = 0

    End With

    With Selection.FormatConditions(1).Interior

        .PatternColorIndex = xlAutomatic

        .Color = 5287936

        .TintAndShade = 0

    End With

    Selection.FormatConditions(1).StopIfTrue = False

End Sub

It does the formatting, but seems to use my original selection as a reference and not the current selection.

I believe it could possibly be the  "=LEN(TRIM(G11))>0" part that always start at G11.

How do I change it to work with my selection instead?

Thanks!

Renaldo

Answer 1

Hi Renaldo,

To deal with the range definition, try replacing

Sub testcolor1()

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=LEN(TRIM(G11))=0"

    

with something like:

Sub testcolor1()

    Dim sStr As String

    sStr = Selection.Cells(1).Address(0, 0)

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

                                       "=LEN(TRIM(" & sStr & "))=0"

===

Regards,

Norman

Answer 2

Thanks mate!

That did it.