Conditional Formatting through VBA

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
This is strange,

Code:
Sub Macro1()
    With Range("A1")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1"
    .FormatConditions(1).Interior.ColorIndex = 3
    .FormatConditions.Add Type:=xlExpression, Formula1:="=A1<>B1"
    .FormatConditions(2).Interior.ColorIndex = 50
    .AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault
    End With
End Sub

If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5

Condition 1 is
=IT65533=IU65533
Condition 2 is
=IT65533<>IU65533

But if I run the code with A1 as my active cell, it gets entered correctly as
Condition 1
=A1=B1
Condition 2
=A1<>B1

Why does this happen ?

Thanks...
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi Jonmo

You are using relative addresses, and conditional formatting considers it relative to the active cell.

Since you are in cell D5 you could use "=D5=E5" and this would be translated in A1 to "=A1=B1".

Since you used "=A1=B1" in D5, excel translates it to "the cell 3 columns to the left and 4 rows up, equal to, the cell 2 columns to the left and 4 rows up".

If you then apply it to A1, "the cell 3 columns to the left and 4 rows up" means that, since A1 is the top left cell, excel has to wrap around to the last columns rows.

Consider the rows/columns as wrapping around:

Columns IS, IT, IU, IV, A, B
Rows 65532, 65533, 65534, 65535, 65536, 1, 2

"the cell 3 columns to the left and 4 rows up" from A1, is IT65533
"the cell 2 columns to the left and 4 rows up" from A1, is IU65533

2 solutions:

1 - (I don't like) refer to the active cell, in this case "=D5=E5" to get "=A1=B1" in cell A1.

2 - I usually use R1C1 notation.

Code:
    .FormatConditions.Add Type:=xlExpression, _
          Formula1:=Application.ConvertFormula("=RC=RC[1]", xlR1C1, xlA1)

Hope it was clear.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Yes, that's clear thanks. Any Idea Why it works like that? Why does conditional formatting in VBA refer to the Active Cell? It just doesn't make sense...
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Any Idea Why it works like that? Why does conditional formatting in VBA refer to the Active Cell? It just doesn't make sense...

No, I only found out the first time I used it and it didn't work. I used the formula just like you did, and after that it was trial and error. I tried afterwards to find some logic or some article on the web that would explain it but had no luck. I found code that uses this knowledge, but no explanation about the why it is like this.

Cheers
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

It's a bug - see this MSKB article.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This uses R1C1 notation and you can apply the Conditional Formatting to the entire range at once:

Code:
Sub Macro1()
    With Range("A1:A29").FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=RC1=RC2"
        .Item(1).Interior.ColorIndex = 3
        .Add Type:=xlExpression, Formula1:="TRUE"
        .Item(2).Interior.ColorIndex = 50
    End With
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

Rory:

Great, I hadn't found yet any article about this. I must say, however, that the workarounds that ms suggests seem a bit lame.

The first doesn't really solve the problem, with absolute addresses you can't copy the formats.

The second doesn't also seem a workaround, it's not "there's an error, this is how we work around it", it's ""there's an error, let's go along with it".

Hotpepper:

I agree with you, it had worked in the tests I made. The reason I didn't post it and will not use it is that I've not yet seen any ms example using the R1C1 notation directly. Until I do I'm afraid it might not work in some case, like with the .FormulaArray property that usually works with A1 but, sometimes, doesn't. Maybe it's just me being too cautious.


Remark: I believe you meant:

Code:
.Add Type:=xlExpression, Formula1:="=RC=RC[1]"

Cheers
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I must say, however, that the workarounds that ms suggests seem a bit lame
I agree, and the fact that they don't suggest using R1C1 notation leads me to think there may well be a 'gotcha' in there too. I can't say I've tested it, but creating a named formula and referring to that in the CF might be an option instead.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I think the difference there is that if you use A1 style references and it is working, you are getting away with something that Microsoft explicity says not to use:


From the help file for FormulaArray:
If you use this property to enter an array formula, the formula must use the R1C1 reference style, not the A1 reference style (see the second example).

The help file for adding an expression to Conditional Formats doesn't specify what format a formula has to take.

From the help file for Add as it applies to the FormatConditions object
Formula1 Optional Variant. The value or expression associated with the conditional format. Can be a constant value, a string value, a cell reference, or a formula.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If you turn on R1C1 references in the workbook, and record a macro, it records it the exact same way, except is using R1C1 style references:

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2008
'
'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=RC1=RC2"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top