Conditional Formatting and Cell Color

[nemadrias]

Here is my issue:
I want to color multiple cells based on certain criteria. For example,
I will select all cells F1:F12 and if the percentage in each cell is
less than the percentage in the same row (different column), color that
cell red. If greater, color it yellow. If =, color it white. I used
conditional formatting, and it is screwing up the colors based on what
I'm sure is my crappy logic. Please revise if you can. Thanks,


=IF((F12>C12), 1, 0) - Formatting color chosen is yellow
=IF((F12<C12), 1, 0) - Formatting color chosen is red
=IF((F12=C12), 1, 0) - Formatting color chosen is white

But the colors are not displaying correctly for the cells where the
values are =. I can elaborate if necessary, I'm assuming its the if
loop that is the prob. Thanks,
Steve

 

[Guest]

For conditional formatting, you just need formulas that return TRUE or FALSE.
Try these:

=(F12>C12)
=(F12<C12)
=(F12=C12)

Hope this helps,

Hutch

 

[daddylonglegs]

If your conditional formatting doesn't give the correct result when C12
and F12 are equal that's probably because the aren't EXACTLY equal.
e.g. if you only display without decimals then 16% could in reality be
16.23%. You could use conditional formatting formulas like

=ROUND(C12,0)>Round(F12,0)

=ROUND(C12,0)<Round(F12,0)

=ROUND(C12,0)=Round(F12,0)

 

[nemadrias]

Thanks! I'm almost positive you hit it on the head there with the
rounding. For example, cell C3 is actually 15.2, but as a percentage
it shows up as 15% in the cell. When I apply the round to the cell
=ROUND(C3,0) it rounds .152 to 0. What I want is for it to round it to
15%. Do you know how to do this? Thanks so much,
Steve

 

[Bob Phillips]

=ROUND(C3,2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks! I'm almost positive you hit it on the head there with the
rounding. For example, cell C3 is actually 15.2, but as a percentage
it shows up as 15% in the cell. When I apply the round to the cell
=ROUND(C3,0) it rounds .152 to 0. What I want is for it to round it to
15%. Do you know how to do this? Thanks so much,
Steve

 

[nemadrias]

Bob -
How can I round all of the cells in that column without creating a
circular reference? I don't want to add a column for the rounded
percentages, just want to round them all to the percent. =ROUND(C3,2)
creates a circular reference obviously if I would paste it in C3. Any
suggestions? Thanks,
Steve

=ROUND(C3,2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Pete_UK]

Do you have a formula already in column C to calculate the percentage?
If so, you can change it to:

=ROUND(your_existing_formula,2)

and copy this down.

Alternatively, incorporate the ROUND function in the conditional
formatting formulae.

Hope this helps.

Pete

 

[Bob Phillips]

With VBA?

For Each cell In selection
cell.Value = Round(cell.value,2)
Next cell

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)