A1 format vs R1C1 format & portability

[Phil_V]

Ok, so I normally develop with Excel set to A1 format.

I have a scenario where my VBA code checks the value of a cell, and then depending on the value the address of another cell is stored.

I then setup some conditional formatting, and had been using that stored address as a reference.

Perhaps clearer with some example code;

Please Login or Register  to view this content.

Now this works FINE when Excel is set to use A1 reference style, but if I change it to use R1C1 reference style, then the code fails when it tries to add the format condition.
It seems that the default of Address is to return in A1 format, which then the re-assignment to the conditional formatting fails on.

So is there any way for me to tell what format Excel is currently using, and therefore know which format I should enter formula using?

Bizarrely I am sure I have entered Conditional Formatting formula in R1C1 format when Excel has been set to use A1 and not had a problem, but I might be wrong

[Cheeky Charlie]

Are you sure this is a format thing and not that a quirk elsewhere in your code is asking Excel to reference column (A - 2) or (B - 2)?

CC

[DonkeyOte]

...is there any way for me to tell what format Excel is currently using

see: Application.ReferenceStyle

[shg]

It seems that the default of Address is to return in A1 format,

It is, but there is the option to return R1C1 format. See Help for the Address property.

[Phil_V]

Thanks all

Shg, thanks, I realised that there was a way to force 'Address' to be in one format or another, but I didn't know how to tell which format was actually required at run-time

Thanks to DO for providing the ref. for that