# Conditional Formatting Help?

#### CzechCzar

##### New Member
Hi all,

I would appreciate a bit of help! I am looking to color-code a nested if statement based on the final values. My statement runs like this:

if(x<=0,"Below Min",if(and(x>0,x<=100%),"In Range",if(x>100%,"Above Max")))

In reality, my statement has several more nested conditions, so that standard stoplight conditional formatting will not work I think.

Does anybody know how to apply conditional formatting so that the color of the text in the cell changes depending upon whether the result value is "Below Min", "In Range", etc.?

MANY THANKS!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi all,

I would appreciate a bit of help! I am looking to color-code a nested if statement based on the final values. My statement runs like this:

if(x<=0,"Below Min",if(and(x>0,x<=100%),"In Range",if(x>100%,"Above Max")))

In reality, my statement has several more nested conditions, so that standard stoplight conditional formatting will not work I think.

Does anybody know how to apply conditional formatting so that the color of the text in the cell changes depending upon whether the result value is "Below Min", "In Range", etc.?

MANY THANKS!

Hope I can be of help, but have you tried using formulas and then the format options??

Check out this video Conditional formatting - highlight entire row based on a cell value

The below is from a thread call "Conditional Formatting Cells based on overlapping range": it could a good example of multiple criteria for conditional formatting.

Quote Originally Posted by BennyH View Post
Tried copying but still getting an error message for formula 1-amber? Maybe a picture describes it best, i want the cell formatting to come back with red if its> +/-10%, amber within 5% to 10% (over or under spent) & green if its within -5% to 5% of budget
:
Budget vs Actual Variance
<-10.1% -10% -5.1% -5% 0% 5% 5.1% 10% 10.1%>
Hi Benny, sorry about the late reply but actually had to do some work!! Shocking turn events.

I redid your project and got the results you needed!!

How I did it was with two formulas, but both using the same colors to achieve the results.

Five formulas were used, I will list them below, from smallest percentage to largest and then go through the steps I took

=\$D2<-10.1% Color Red

=AND(\$D2<=-5%,\$D2>=-10.1%) Color Yellow

=AND(\$D2>=-5%,\$D2<5%) Color Green

=AND(\$D2>=5%,\$D2<=10.1%) Color Yellow

=\$D2>10.1% Color Red

Steps taken to achieve results.

1) Highlight cells A2 too D6
2) Click on Conditional Formatting, Manage Rules
3) Click on New Rule
4) Click on Use a Formula
5) Paste formulas in to box
6) Click on the format box and choose your color
7) Click OK twice and there you have it

Make sure you remove the second \$ and you have the <=>signs in their correct place and that should do it!!

Hope that points you in the right direction!

Note that the first two rules have the box "Stop If True" checked.

Sheet1

 A 1 98% 2 -71% 3 -47% 4 185% 5 93% 6 101% 7 -25% 8 0% 9 158% 10 -40% 11 -44% 12 85% 13 142% 14 144% 15 -42% 16 59% 17 170% 18 200% 19 81% 20 -41% 21 75% 22 180%

<tbody>
</tbody>

Conditional Formats
 Cell Nr.: / Rule Format A1 1. / Formula is =A1<0 Abc A1 2. / Formula is =A1<=1 Abc A1 3. / Formula is =A1>1 Abc

<tbody>
</tbody>

<tbody>
</tbody>

Simplest method I can recommend is setting up three conditional formatting rules to satisfy your needs.

1) If(x<0,TRUE,FALSE)
2) If(and(x<0,x<=1),TRUE,FALSE)
3) If(x>1,TRUE,FALSE)

Set each rule to the formatting you desire and apply to each cell. When copying formatting to multiple cells, make sure you go back into the formula for each rule and remove the cell lock "\$" as appropriate. If you are using a formula to spit out a string, i.e. "Below Max" then subsitute the variable with the string.

1) If(\$A\$1 = "Below Min",TRUE,FALSE)
2) If(\$A\$1 = "In Range",TRUE,FALSE)
3) If(\$A\$1 = "Above Max",TRUE,FALSE)

Again, set this up for the first cell then go back and remove the '\$' then copy it and "Paste Special - Formatting" to the cells you want it to apply to.

HTH,
-G

Many thanks all. This is tremendously helpful. I now know how to apply conditional formatting!

Replies
6
Views
110
Replies
8
Views
431
Replies
1
Views
276
Replies
4
Views
254
Replies
0
Views
95

1,196,517
Messages
6,015,682
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back