Conditional Formatting Exception

[ebilbrough]

Column A is ProjectName
Column B is Personnel (JohnSmith, or MaryJones, etc.)
Columns C - Z are weekly hours worked.

The data extends for 1200 rows.

Each project has a differing amount of rows (dependent upon the number of personnel working that project).
The projects (rows) are grouped and there is a SUBTOTAL line (row) after each project grouping.

I need to create a conditional format for all the individual personnel hours cells (C2:Z1200) but need to exclude the formatting of the SUBTOTAL row.

Thanks.

 

[Joe4]

Whatever your current Conditional Formatting is, just wrap it into and AND statement that checks the column/cell where the word SUBTOTAL would appear, i.e.

=AND($C2<>"SUBTOTAL",[I]your current formula[/I])

 

[ebilbrough]

Thanks Joe!

 

[Joe4]

You are welcome!

 

[ebilbrough]

When I use the following formula in my conditional format:

=AND($A2<>"Subtotal*",>40)

I receive an excel message: We found a problem with this formula. Try clicking.....

If I put double quotes around the >40 (">40") it excepts the formula but doesn't change the cell.

 

[Joe4]

Yon need to be explicit in your range references when using the formula options.
What cell are you checking to see if it is greater than 40?
If it is column B, then the formula would look like this (for row 2):

[COLOR=#333333]=AND(LEFT($A2,8)<>"Subtotal",$B2>40)[/COLOR]

Also note that I don't think it will like wildcards, so I changed your formula to see if the first 8 characters are not "Subtotal".

 

[ebilbrough]

Thank again Joe. Works great.

 

[Joe4]

You are welcome!