Help with Excel Conditional Formatting Through VB.NET

diablo089
diablo089 used Ask the Experts™
on
I have a conditional formatting formula that works fine in native Excel, but comes out weird when I use VB.NET to place it in a range of cells.

Here's the formula in native Excel 2007:
=AND(ISBLANK(J5), NOT(ISBLANK($I$5)))

I set the applies to to this: =$B$5:$D$5,$J$5:$AC$5

So this works for what I'm trying to do: highlight columns B-D and J-AC of a single row if it is blank, and column I is not blank.

In VB.NET, to accomplish this I put the following code inside a loop that loops through the number of rows I want, where oBook is my workbook, and j is my loop counter

oBook.Sheets("PAD DATA").Range("B" & j & ":D" & j & ",J" & j & ":AC" & j).FormatConditions.Add(Type:=2, Formula1:="=AND(ISBLANK(B" & j & "), NOT(ISBLANK($I$" & j & ")))")
oBook.Sheets("PAD DATA").Range("B" & j & ":D" & j & ",J" & j & ":AC" & j).FormatConditions(1).Interior.PatternColorIndex = -4105
oBook.Sheets("PAD DATA").Range("B" & j & ":D" & j & ",J" & j & ":AC" & j).FormatConditions(1).Interior.ThemeColor = 10

Open in new window

:

When I run this in VB.NET, in Excel I get a conditional formatting formula of
=AND(ISBLANK(XX1048565), NOT(ISBLANK($I$5)))

So how can I get the formulas to come out the way it is when I add the conditional formatting natively in Excel? Should I try looping through each particular cell and adding conditional formatting to it? Also, is there an easier way to do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Why loop? You don' t need multiple format conditions, you just need one with the appropriate reference.

Identify your last row and get rolling

Thomas

lastrow=25

oBook.Sheets("PAD DATA").Range("J5:AC26,B5:D"& lastrow).FormatConditions.Add Type:=xlExpression, Formula1:= "=ISBLANK($J5)*NOT(ISBLANK($I5))"
oBook.Sheets("PAD DATA").Range("J5:AC26,B5:D"&lastrow).FormatConditions(1).Interior.PatternColorIndex = -4105
oBook.Sheets("PAD DATA").Range("J5:AC26,B5:D"& lastrow).FormatConditions(1).ThemeColor = 10

Open in new window

Author

Commented:
What I'm trying to accomplish is this... I'm trying to highlight any cells in columns B-D and J-AC if they are blank when I is not blank, and each row should reference the cell in column I of that particular row. Will this work for that?
Top Expert 2008

Commented:
it should because it doesn't have the $. so the formatting will follow row by row.

Thomas

Author

Commented:
Hmm... I can't quite figure out how to make this work.

This is close, but anchors to column B in the formula, obviously:
oBook.Sheets("PAD DATA").Range("B5:D" & LastRow & ",J5:AC" & LastRow).FormatConditions.Add(Type:=2, Formula1:="=ISBLANK($B5)*NOT(ISBLANK($I5))")

This creates a weird situation where the ISBLANK(B5) becomes ISBLANK(XEX5):
oBook.Sheets("PAD DATA").Range("B5:D" & LastRow & ",J5:AC" & LastRow).FormatConditions.Add(Type:=2, Formula1:="=ISBLANK(B5)*NOT(ISBLANK($I5))")

I'd like the ISBLANK() portion to reference the current cell. Is there some way to tell Excel to do this?

Author

Commented:
If I could just get the stupid thing to use the formula:

=ISBLANK(B5)*NOT(ISBLANK($I5)) I'd be set.

I don't know why

oBook.Sheets("PAD DATA").Range("B5:D" & LastRow & ",J5:AC" &  LastRow).FormatConditions.Add(Type:=2, Formula1:="=ISBLANK(B5)*NOT(ISBLANK($I5))")

produces

=ISBLANK(XEX5)*NOT(ISBLANK($I5)) in Excel
Top Expert 2008

Commented:
you need $J5 in there to fix the column.

oBook.Sheets("PAD DATA").Range("B5:D" & LastRow & ",J5:AC" &  LastRow).FormatConditions.Add(Type:=2, Formula1:="=ISBLANK($J5)*NOT(ISBLANK($I5))")

Thomas

Author

Commented:
Well, I don't know. If I anchor to J5 if any of the other cells in B-D and J-AC of that row are filled while J5 is blank, they still turn orange.
NorieAnalyst Assistant
Commented:
I don't think this is a VB.NET problem, it's a problem you can experience when trying to set conditional formatting with formulas using VBA too.

The only solution(s) I ever found where:

1 Run the code multiple times, not ideal obviously.

2 Use R1C1 notation for the formulas instead of A1.

Don't ask me exactly why either works but they do.

I would say 2 is the best solution though converting to R1C1 can sometimes be tricky.

By the way I think Thomas is right about the loop - it shouldn't be needed, just like formulas in a cell the formulas in CF should adjust accordingly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial