Conditional formatting with VBA

Question

I'm using Excel 2010 and need a VBA macro that assigns conditional formatting to a group of cells using offset references to those cells. 

The following code clears any existing conditional formatting using offset references to cells B4:M4.   It then conditionally sets the format of those cells based on their contents being non-numeric with direct references to the cells.

' Set up offset cell reference

    Range("$a$4").Select

    ActiveCell.Offset(rowOffset:=0, columnOffset:=0).Range("B1:M1").Select

    Selection.FormatConditions.Delete

' Test for non-numeric cell contents using direct cell reference

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(ISNUMBER(B4:M4))"

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

    With Selection.FormatConditions(Selection.FormatConditions.Count).Font

        .Bold = False

        .Color = 0                              'Non-bold Black type

        .TintAndShade = 0

    End With

    With Selection.FormatConditions(Selection.FormatConditions.Count).Interior

        .PatternColorIndex = xlAutomatic

        .Color = 682978                         'Brown background

        .TintAndShade = 0

    End With

    Selection.FormatConditions(Selection.FormatConditions.Count).StopIfTrue = True 

I would like to replace the direct reference formula with one using an offset reference.

Can you suggest a solution?

Thanks.

Answer 1

Hi,

try this which formats the range as an offset from the activecell

Sub CF_Cells()

'Set up offset cell reference

Set Rng = ActiveCell.Offset(, 1).Resize(, 12)

    With Rng

        .FormatConditions.Delete

        ..FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(ISNUMBER(" & Rng.Address(0, 0) & "))"

        .FormatConditions(Rng.FormatConditions.Count).SetFirstPriority

    End With

    With Rng.FormatConditions(Rng.FormatConditions.Count).Font

        .Bold = False

        .Color = 0                              'Non-bold Black type

        .TintAndShade = 0

    End With

    With Rng.FormatConditions(Rng.FormatConditions.Count).Interior

        .PatternColorIndex = xlAutomatic

        .Color = 682978                         'Brown background

        .TintAndShade = 0

    End With

    Rng.FormatConditions(Rng.FormatConditions.Count).StopIfTrue = True

End Sub

Edit.. Change to remove bug in range

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 2

This worked.  Thanks very much.