Excel 2007 - VBA - Apply Conditional Format on Range Based on Cell Values

0
votes

For some reason, I can't figure out why the same formula that I can apply to conditional formatting regularly doesn't work in VBA. I'm trying to highlight the row in that range if one of the cells in each row is not blank.

Below is the code I'm working with:

With Range("$A3:$L1000")
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=($J3)<>"""""
.FormatConditions(1).Interior.ColorIndex = 46

End With

When it applies to the document, here's the formula in Conditional Formatting enter image description here enter image description here

Any help/advice you all could provide would be greatly appreciated.

1
vbaexcel
Can't reproduce over here. – dwirony
What is the active row when you apply the CF? since the row number is relative, it will be relative to the current row, so it will matter a lot where the current selection is. It must be in row 3 for the CF to work correctly. – teylyn
I've had this happen to me on a number of occasions -- the cell range in the Rule or the Applies To gets changed to an extremely large number. My only fix has been to go in and edit by hand to fix it. – PeterT
The extremely large number would be the one of the last rows of an Excel sheet. This happens when a relative reference to a cell above the current cell is applied to row 1. – teylyn

1 Answers

0
votes

Update:

I solved this by recording a macro where I assigned the conditional formatting to a single line, copied the formatting, then pasted it over the active range.

Thanks to everyone for your input, and hope this helps someone else down the road!

Code

Range("A3:M3").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=($J3)<>"""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False