Conditional Formatting applied using VBA results in incorrect reference

[DJAXE]

The search I ran for "conditional formatting" produced a lot of threads with an unhelpful title, so I've crammed as much detail as I can into the heading.

This is not a question, rather I'm just posting it in case someone else comes up with the same problem. I've found one other reference to this issue on the 'net, but it was unresolved.

Basically, I had a piece of code which was applying Conditional Formatting to Excel cells (which was based on a Formula) but it was changing the row references.

I even tried recording a macro to get the result I wanted, but it still failed.

For example: I recorded the following macro (unnecessary lines deleted):

Sub Macro1()
    Range("A1").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B3=1"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub

In the Conditional Formatting dialog for cell A1 I'd have the same formula, but the row number was offset by 3, upwards (kind of) and produced:

=B65533=1

It was truly bizarre and seemingly no matter what I did or what combinations I tried, I got the same result. Regardless of which cell I applied the formatting to, the row in the resulting formula was three rows above the one I specified.

Anyway, the resolution appears to be simply to re-start Excel because all that happened yesterday and now it's not happening.

Very strange.

Version of Excel: 2003 SP3 11.8332.8333
VBA Version 6.5.1053

Again, this is not a question - although if anyone else has seen something similar and got a more technical answer/fix than mine, I'd love to hear about it.

 

[Jonmo1]

It works for me...

But try not using Select and Selection.
You never know when "Selection" is not necessarily what you think it is.

Try working with the range directly..

Sub Macro1()
With Range("A1")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=B3=1"
    .FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

 

[DJAXE]

Thanks for the response, but I wasn't actually asking for assistance, the code was just an example of something I tried.

 

[Norie]

I'm confused - do you want a fix or not?

 

[DJAXE]

Sorry, I thought I was pretty clear in my original post.

Again, this is not a question - although if anyone else has seen something similar and got a more technical answer/fix than mine, I'd love to hear about it.

My "fix" for the offset error I encountered amounted to restarting Excel.

The sequence of events was:
1. I wrote some code to apply conditional formatting (which was referring to the cell directly, not a selection).
2. The conditional formatting ended up with a formula offset by 3 rows.
3. I tried recording a macro to see if it was Excel or something in my code (example in original post), but it produced the same result.
4. The next day, the problem had disappeared.
5. I posted the problem/resolution here because I found only one other reference to it on the 'net but with no "fix".

 

[Norie]

I apologise if I misinterpreted the post.

I've definitely seen something similar when using code to set conditional formatting involving a formula.

I found something that worked for me but I've no idea if you would call it a 'fix' exactly.

What I found was that if you converted the formula to use R1C1 notation the code worked first time.

The references in the formula were correct, the conditional formatting worked as it should...

The hardest part, for me anyway, was the conversion of the formula from A1 to R1C1 notation.

Is that any use?

PS If you can explain the logic of the conditional formatting you are trying to apply I'd be happy to post some code.

 

[DJAXE]

That's a good idea, Norie. At the time, I just put it aside because it wasn't an essential part of the functionality.

I had to convert my range names from a Row/Cell number to create the range address for the Conditional Formatting, so there'd be no problem going back to R1C1 formatting in that code.

Cheers. Hopefully anyone else who finds this problem will have this thread come up on their search results and they can benefit as well.

 

[Norie]

Do you mean you converted to using Range from Cells for the range to apply the formatting to or the formula?

 

[Michael Seemann]

This is a known Excel bug, see http://support.microsoft.com/kb/895562. The solution is to use absolute references:

Sub Macro1()
With Range("A1")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$B$3=1"
    .FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

If you wish to apply the CF to a range of cells, you need to set up a loop:

Sub Macro1()
Dim i as Integer
For I = 1 to 10
    With Range("A" & i)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$B$" & i & "=1"
        .FormatConditions(1).Interior.ColorIndex = 3
    End With
End Sub
Next i

 

[Michael Seemann]

Here is another workaround, by selecting the upper left cell in the CF range:

Sub Macro1()
Range("A2").Select
With Range("A2:A10")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$B2=1"
    .FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

For a thorough explanation, see excel - When setting a FormatCondition on a range which is not selected, this fails? - Stack Overflow