Conditional Formatting 'Applies to' range change without input?

[noboffinme]

Hi,

I'm working on an Excel 2007 file that has a Column of Data Validation Drop Down lists in Column 'A'.

Depending on the value selected from the Drop Down, I have added Conditional Formatting (CF) rules to shade the row (Up to Column 'R') a certain colour.

I've set this up for about 20 different values to display different colours for the Row.

I am finding that the CF 'Applies to' ranges changes without any input from me & therefore the CF applies where I don't want it.

Can anyone explain why this happens & a possible workaround/fix?

I've tried applying a 'Named Range' to the 'Applies to' area but it reverts to the range.

Thanks

 

[noboffinme]

Sorry, I should add that I originally applied an 'Applies to' range of $A6:$R500 for this CF to make the changes but it changes, by itself to all sorts of cell ranges as well making the 'Applies to' range work inconsistently.

So the original $A6:$R500 changes to something like $A26:$A501 without any input??

 

[lemo]

i always have problems with conditional formatting ranges, they jump around and/or create new ranges if you start inserting/deleting/copying rows/columns/cells.

try using $A:$R as your range, if you are ok with applying it to rows before 6th and after 500th, it may help.

 

[noboffinme]

Thanks lemo,

That's a good idea,I'll give it a try.

Any idea why it happens??

I can't find any pattern as to why they change...

Any Microsoft people out there that know why this happens??

Thanks

 

[lemo]

i'd guess it's just programmed that way.
why it's programmed that way is another question.

 

[Brianwarnock]

This appears to be an issue discussed on many forums with no real explanation.

Brian

 

[noboffinme]

Thanks lemo / Brian,

I have enhanced this report for a Colleague & he is the one having the problems.

The version I have is fine.

I can't pinpoint anything he is doing that would cause this ???

Microsoft Customer Service advise they're not aware of the issue.

One other question, what exactly is the 'Stop if True' checkbox doing?

Thanks Peter

 

[lemo]

i think 'Stop if True' does exactly what it says - it stops all the conditional formatting rules BELOW the checked check-mark.

i don't recall ever using it, conditional formatting is finicky enough as is, i generally try not to complicate it even further.

 

[noboffinme]

Just a follow up note as I have found the cause of this 'anomaly'.

I created this file for a colleague & after sitting with him watching how he used it, I found he was inserting & deleting Rows in the file & this was the cause of the Conditional Formatting Range changes...

Mystery is solved !!