# Conditional Format: Change colors for dif. values not workin

#### Kwest

##### New Member
Hi,

I’ve been checking the topics for conditional formatting answers to my problem and I have not found one to meet my needs. Here is what I am trying to do…

I am calculating service levels in number of minutes my team took to resolve an issue. I am trying so setup conditional formatting so that if the number of minutes is lower than the goal the cell will color green and if higher than the goal it will color red. Of course there are several different types of issues that are resolved so there are different numbers of minutes for each goal. I calculated the goal by using a text to value formula. Here is that example: Cell E4 =IF(D4="other","1440", IF(D4="Hardware","2880", IF(D4="Passwords/Logon IDs", "720", IF(D4="Telephone", "1440", IF(D4="PBX", "1440", IF(D4="Application", "1440"))))))

So let’s say that E4 shows up as 1440. Cell C4 is the total number of minutes it took to resolve the problem. I set up conditional formatting to show criteria 1 as “cell C4 is less than E4 = Green” and criteria 2 as “cell C4 is greater than E4 = Red”. Sounds easy enough…but it doesn’t work. Therein lies the problem… If I try to drag that format down the sheet it wants to use cell E4 only as the base number to measure against. So…I found a formula and tried to set up conditional formatting like this: criteria 1 =IF(C4<E4,1,0) “green” and criteria 2 =IF(C4>E4,1,0) “red”. That works great for dragging as it changes the cell numbers to provide the base number to measure against. But…the color is not changing to “red” when I get down to a point where the cell value is higher. Cell C15 is 2890 and E15 is 2880. C15 should show “red” but is showing “green”. I checked conditional formatting for that cell and it is using the proper cells. Is this happening because of the crazy IF formula that I am using in the E column? Is there a better way? Thanks!!

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Re: Conditional Format: Change colors for dif. values not wo

I can see that the lower formula did not show up right. Here it is:

So…I found a formula and tried to set up conditional formatting like this: criteria 1 =IF(C4<E4,1,0) “green” and criteria 2 =IF(C4>E4,1,0) “red”.

Re: Conditional Format: Change colors for dif. values not wo

I can see that the lower formula did not show up right. For some reason it does not like my less than sympol. So here it is:

So…I found a formula and tried to set up conditional formatting like this:

criteria 1 =IF(C4 is less thand E4,1,0) “green”
criteria 2 =IF(C4>E4,1,0) “red”.

Re: Conditional Format: Change colors for dif. values not wo

Okay...I'm a dummy. I figured it out!! The key to this one is that in my "IF" statement I was returning the values as text and not a number. This is because I was using the quotation marks around the value. For example this is what I was using:

=IF(D4="other","1440") and I changed it to
=IF(D4="other",1440) and it works!!

Woohoo! Thanks for looking!

Replies
3
Views
79
Replies
3
Views
326
Replies
3
Views
552
Replies
2
Views
99
Replies
12
Views
217

1,203,099
Messages
6,053,526
Members
444,669
Latest member
Renarian

### 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