| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting and Cell Color
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((F12C12), 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
For conditional formatting, you just need formulas that return TRUE or FALSE.
Try these: =(F12C12) =(F12<C12) =(F12=C12) Hope this helps, Hutch "nemadrias" wrote: 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((F12C12), 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565769 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
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 daddylonglegs wrote: 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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565769 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
=ROUND(C3,2)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "nemadrias" wrote in message ups.com... 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 daddylonglegs wrote: 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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565769 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
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 Phillips wrote: =ROUND(C3,2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "nemadrias" wrote in message ups.com... 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 daddylonglegs wrote: 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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565769 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
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 nemadrias wrote: 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 Phillips wrote: =ROUND(C3,2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "nemadrias" wrote in message ups.com... 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 daddylonglegs wrote: 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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565769 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Formatting and Cell Color
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) "nemadrias" wrote in message oups.com... 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 Phillips wrote: =ROUND(C3,2) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "nemadrias" wrote in message ups.com... 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 daddylonglegs wrote: 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) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=565769 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Formatting Conditional On Other Cells Fill Color? | Excel Worksheet Functions | |||
| Conditional Formatting to Test Value in Cell | Excel Worksheet Functions | |||
| Cell color change without using conditional formatting | Excel Discussion (Misc queries) | |||
| Cell color based upon cell value | Excel Discussion (Misc queries) | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
