Conditional Formatting and Cell Color

N

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
 
T

Tom Hutchins

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

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

Hope this helps,

Hutch
 
D

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)
 
N

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
 
B

Bob Phillips

=ROUND(C3,2)

--
HTH

Bob Phillips

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

nemadrias said:
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
 
N

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


Bob said:
=ROUND(C3,2)

--
HTH

Bob Phillips

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

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top