Only apply conditional formatting if a condition is met

[maddogp]

I have a spreadsheet that has a few different columns: Name, #, Date, and then "Zones" broken down into different categories (painted, cut, assembled, etc) with a "target" and "status" column for each category.

All of the zone information is inputted manually, as a percentage. So for each name, there will be a target percentage and an actual status percentage for each of the categories in each zone. These are all currently being manually formatted to different colours, and I want to make this an automatic rule... somehow.

Target percentage is almost always 100%, but for new projects it is sometimes lower. When the status percentage is less than the target percentage, BOTH the target and status boxes are coloured yellow. If target and status values are equal (almost always both 100), then both cells are formatted green.

I know that I will have to use conditional formatting of "if this cell > this cell", format yellow, my problem is the "applies to" box. Do I have to create a rule for every category and every project? Or is there an easier way to do this?

The other problem with this is that below all of the project values, there are formulas that use the status averages in the same column, but below all of the project data. The ONLY column that is not used for something else below it is the # column (column B). So I have to check if column B is blank, and if it is, then to stop colour-formatting at whatever that row is.

Any suggestions?

[FDibbins]

Do I have to create a rule for every category and every project? Or is there an easier way to do this?

If the same rules apply to various ranges of data, you can set it up that way in the "Applies To" window, something like this...
=$D$1:$E$10,$H$1:$I$10,$K$1:$L$10

Regarding the rest of your question/s, I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[maddogp]

Here is an example with the data coloured the way I want it, done manually. The actual spreadsheet has at least 50 projects on it for any given week, so I would like to keep manually entering conditional "applies to" boxes to a minimum.

Sample.xlsx

[FDibbins]

This is the approach I took...
1st, format all cells yellow using regular formatting (that way, you only need 1 CF rule)

1. click on the 1st cell (D3)
2. Select CF/New Rule/Use Formula
3. enter =D3=E3
4. Format file GREEN
5. click OK
6. In Applies To, highlight the 1st range (D and E columns), hold CTRL and hight the next range, rinse and repeat for the other ranges