Conditional Formatting and Blank Cells

**Pixel_Donkey** · 05-19-2011, 04:30 AM

Hello,

I am entering a value between 1/10 and 10/10 and sometimes the field is blank.

I have conditional formatting setup as follows:

Condition 1: [Cell Value is] [Less Than] ="5/10" (Displays a red background)
Condition 2: [Cell Value is] [Greater Than or Equal to] ="5/10" (Displays a green background)
Condition 3: How can I set this so if the field is blank, the background is white?

If you could help me I would be very grateful.

**TMS** · 05-19-2011, 04:42 AM

See the attached image.

Note that you need to test for a blank cell first as blank will be treated as 0 and less than 0.5.

white: =$B1=""
red: =$B1<0.5
green: =$B1>=0.5

all applied to: =$B$1:$B$7

Regards

**Pixel_Donkey** · 05-19-2011, 04:57 AM

I've tried to do that but it doesn't seem to be working, am I doing something wrong?

Screenshot and xls attached.

Thanks

**TMS** · 05-19-2011, 05:15 AM

Excel isn't seeing the data as fractions. Not entirely sure what it is seeing, to be honest.

Anyway, if there's not too many cells, go into each one starting with A1, press F2 and Enter.

Then select all the cells and format them as Fraction with up to three digits.

You should then see colour coded fractions.

Note that 6/10 will show as 3/5, etc.

Also note that your CF doesn't cater for 5/5 (1/2) and doesn't apply a colour.

Regards

**Pixel_Donkey** · 05-19-2011, 05:27 AM

Hi TMShucks,

Thats excellent thank you!

I've never pressed F2 before - don't actually know what it does but it works.

Changed the cell format to "As Tenths" in cell format and they all show correctly except 10/10 which is 1 but thats not a major issue.

I fixed by CF so 5/10 shows green... =$A10>=0.5

Thank you for your help

**TMS** · 05-19-2011, 05:33 AM

You're welcome.

F2 just takes you into Edit mode so you can change the cell contents. Pressing Enter just confirms the contents without changing anything, but Excel then recognises the contents as a fraction ... although displaying it as a decimal.

Glad you could find a suitable format and were able to adjust the CF.

If this has answered your question, please mark the thread as Solved. See my signature for details or the FAQ.

Regards