Home » Blogs » kashifulhaq's blog

Conditional Formatting Using Formula

Submitted by kashifulhaq on 19 February, 2014 - 09:40

I wish to have the following conditional formatting, but I am not able to correctly put the formula. Can anyone help me with this?

Cell F39:
Color code "Green" if F39 contains text "N/A" or "Yes"
Color code "Amber" if F39 contains text "No" & F38>=1<=50000
Color code "Red" if F39 contains text "No" & F38>50000

Cell F38:
Color code "Green" if F39 contains text "N/A" or "Yes"
Color code "Amber" if F39 contains text "No" & F38>=1<=50000
Color code "Red" if F39 contains text "No" & F38>50000

Cell F37:
Color code "Green" if F39 contains text "N/A" or "Yes"
Color code "Amber" if F39 contains text "No" & F38>=1<=50000
Color code "Red" if F39 contains text "No" & F38>50000

»

One Solution without using VBA

Submitted by tiedied61 on 19 March, 2014 - 20:39.

I used cells A1:A3 and entered text "N/A", "Yes", "No", respectively

in cell C39 use data validation Type List with range of A1:A3 to ensure correct data entry

I used cell B1 and entered this formula:

=IF(C39="No",IF(C38>50000,3,IF(C38>0,2,0)),1)

The results will be 0 = No Color, 1 = Green, 2 = Amber, 3 = Red

Finally, create 3 different conditional formats using formula:

=$b$1 = 1 ... fill color Green
=$b$1 = 2 ... fill color Amber
=$b$1 = 3 ... fill color Red

set each of these conditional formats for the cell range of C37:C39

I would have inserted an image, but I didnt know how in this blog, lol

Art

»

Also...

Submitted by tiedied61 on 19 March, 2014 - 20:42.

I have ads showing up in my browser... be sure to look to the right of the ad for additional text (if you have ad in the way) else you will miss some important stuff

»