Conditional Formatting Doesn't Work

Question

I have been working on tihs for hours, and I know it shouldn't be this difficult. I have a spreadsheet with a formula in a column that calculates the percentage of overtime of total hours worked. If that percentage is greater than 10%, I would like for the cells to be shaded. The problem is that whenever I use the formulas, I get eihter an error message or it shades the entire column, regardless of the value. I'm using excel 2010.  My steps are:

1. highlight the column where I want to apply conditional formatting.
2. choose conditional formatting, new rule, use formula to determine which cells to format
3. type in formula space =cc4>=10%
4. choose the shading/fill color
5. click okay and okay.

Result is the entire column shaded red, even though only one cell has any number!!!!!! Even when I choose to use a regular conditional formatting option without formulas, the result is the same.

Can anyone tell me what is going wrong with this? All the online tutorials I've viewed follow the same steps, but with the correct results.

Answer 1

Hi,

In your first step, do not select the entire column - instead select the first cell which you want to format.  Follow steps 2-5.  Copy the first cell, select the range, right click > Paste Special > Formats > OK.

Hope this helps.

Answer 2

you're very welcome.  it's not an IF formula to say this or otherwise.  it's an AND formula.  the 2 conditions must be met for it to return a TRUE

=AND(CC4>=10%,CC4<>"")

that means CC4 must be greater or equals to 10% AND CC4 cannot be equals to a blank.

to help others with the same problems, could you help mark this as Answer if it solves your problem?  Thanks.  =)

Edit: this seems to be in the discussion area, so maybe click on "Like"

Answer 3

Hi,

Ensure that in conditional formatting, there is no $ before the row number.  So. in the conditional formatting formula bar, the formula should be CC4>=10% (not $CC$4>=10%).

Also, try this

1. Select the range of cells and go to Home > Conditional formatting > Highlight Cells Rules > More Rules > Greater than equal to > 10%
2. Click on Format > Fill and choose colour
3. Click on OK/Apply

Hope this helps.

Answer 4

Thank you for replying. I did this, and it still results in the entire column being shaded read.

Answer 5

hi Camie.  one possible reason is that your formula in CC4 might have returned text?  it could be something like:

=IF(CA4="","",CB4/CA4)

so your formula would fail.  if that is so, try:

=AND(CC4>=10%,CC4<>"")

i've attached a file to show you.  in column CD is your original formula.  you can see that it turns TRUE even for CC6.  that's because i used the above formula to return blank.  CC7 is not highlighted because i didn't use the formula.

https://skydrive.live.com/redir?resid=C4ACBCF446DAF760%21120