How to changing conditional formatting once applied

I have a spreadsheet that management team uses to track resource usage over time. Each resource has conditional formatting applied.
Green 0 to 20% available
Yellow between 21 and 75 %
Red > 75%

Management has asked to change these values to
Green 0 to 40% available
Yellow between 41 and 70 %
Red > 70%

There are now close to a hundred rows (and growing) with conditional formatting applied. When I try to make changes using Conditional formatting - Manage Rules - This Worksheet" I see three rules (Green, Yellow, Red ) for each resource entry. That's ~ 300 changes I need to make.

To make matters worse, there are rows interspersed in the worksheet where conditional formatting isn't applied.


How can I reset the conditional values without having to adjust each one individually?
Is there a way to change all "Green" rules, then Yellow then Red?

Manage Conditional Formatting window. see http://www.exceluser.com/solutions/custtasks.htm
The dropdown that askes for rules in Selection vs Worksheet may be helpful.

It really sounds like you need better Conditional Formatting Rules to limit how many you have. Absolute vs Relative rules can cut the number a lot. This is discribed in article above.

http://office.microsoft.com/en-us/ex...010342674.aspx has some suggestions.

Lastly - I've found Named Ranges has helped me with rules. If you select a bunch of cells and give them a name like "ProfitFromWest" and then use this name in the CF formula it might reduce your number of CF formulas. See at http://www.cpearson.com/excel/cformatting.htm

Without having a look at your sheet and CF formulas, we can only direct you to articles. To show your problem you need to attach the file (with some sample data). Click on "Go Advanced" and use the Paperclip Icon to upload the file.

JBeaucaire, I've tried your suggestion but didn't have success. I was a bit lost with step 3, locating the Primary cell.


I've attached a sample spreadsheet with the conditional formatting and data.

You'll note the multiple entries for Green, yellow and Red conditions as well as a column conditional format to highlight alternating columns gray.

Steve

Hi mgia0055,

I see only part of you sheet is conditionally formatted. Much of it just has background cells colored.

that's correct. Only the "Total Allocation" rows have conditional formatting as does the "Forecast" and "Actual" columns.