Conditional Formatting 'Applies to' ranges changes without any 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.

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

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

I've also tried applying a 'Named Range' to the 'Applies to' area instead, but it just reverts to the original range format so probably wouldn’t work...

Thanks

[ajryan88]

Hi,

Perhaps the problem lies with your absolute references in your range.

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hope this helps

[noboffinme]

Thanks ajryan88,

I've tried this also but I still get the same additional ranges added into the 'Applies to' area.

I wondered what was causing this or if it was a known issue with Conditional Formatting.

[noboffinme]

It might help if I give more specific info as the problem might be what I'm doing rather than an issue with the 'Applies to'Range.

I have 20 different meeting types (CEO, Deputy CEO etc ...) & this Conditional Formatting (CF) shades the Row depending on the Meeting type as follows;

Please Login or Register  to view this content.

There's also another rule for the SAME meeting type, where the same meeting type appears on the row Above, the row is shaded the same colour, but the Font is also shaded the same colour (so it isn't visible)

The Formula applied is as follows;

Please Login or Register  to view this content.

I then apply the Range as $A$6:$R$20000 to all of these criteria for the 20 meeting types.

I also check the 'Stop if True' checkbox - not sure if this is the right thing to do as this file will only be used in 2007 or later.

Thanks for any suggestions & apologies for not being clearer earlier.

[ajryan88]

It does sound like strange behavior.

I'm really sorry, I'm not sure what the problem is here. I hope someone comes along who is able to give you a solution to your problem.

[noboffinme]

Hey don't apologise, all ideas / help welcomed !!