# Solved: Conditional Formatting: Ignore Blank Cells in Conditional formatting



## Mr-Awesome (Jul 29, 2011)

Howdy!
okay so I'm creating a new spreadsheet for work and have to format the cells in column C labelled No. Accidents. if the number of accidents is less than 5 i want it to be green and if its equal to 5 I want it to be yellow and more than 5 i want to be red. 
That part I am fine with
BUT
it counts blank cells as 0
so I want it to be able to ignore these blank cells and leave them as white and not be affected by the conditional formatting colours
Please Help!
Cheers


----------



## Enanito01478 (Jul 27, 2011)

You could set the conditional formatting like this:


If the value in the cell is between 1 and 4, fill cell with green
If the value in the cell is equal to 5, fill cell with yellow
If the value in the cell is greater than 5, fill cell with red.
This leaves blank cells as white and stays within the limit for conditional formatting.


----------



## Mr-Awesome (Jul 29, 2011)

but then if there are 0 accidents in that week then i would need the cell to be green if 0 were to be entered
is there not a formula I could put in so it doesnt affect the blank cells i swear I've seen a formula for it before but I can't remember where and I've been searching google all day for it


----------



## Enanito01478 (Jul 27, 2011)

You may need to add some VBA code then. Here's a code sample that changes the style of a cell. You can create styles under Format -> Styles. Once you have created all the styles that you want, you can use this code in the Worksheet_Change event to change the format of the cell whenever you enter a value.


```
Private Sub Worksheet_Change(ByVal Target As Range)
    If (IsNull(Target)) Then
        Target.Style = "Normal"
    Else
        If (Target.Value >= 0 And Target.Value < 5) Then
            Target.Style = "CustomGreen"
        Else
            If (Target.Value = 5) Then
                Target.Style = "CustomYellow"
            Else
                If (Target.Value > 5) Then
                    Target.Style = "CustomRed"
                End If
            End If
        End If
    End If
 
End Sub
```
Just a quick note, if you clear the value from a cell, it doesn't actually change the style back to "Normal". Not sure why, but probably because it's not a NULL value that's in the cell when the value is deleted.

EDIT: You probably don't want apply this to the whole worksheet and I forgot to include something like that in my code. I can't find that out right now, but hopefully another forum member can assist with that


----------



## Pedro15 (Oct 5, 2008)

Just played around with some options through google.

Assume C1 is where you want formatting, 
First go to Custom format and enter
[Black][<=0]0;[Green][<5]0;[Red]0

Then in Conditional Format 
Condition 1 =(C1=5) format Yellow font
Condition 2 =(C1>5) Format Red font

If you want the cell to be filled the following may assist

Condition 1 =(c1<0) Formatted as follows
Pattern : no colour
Border : no border
Font : Automatic

Condition 2 = Cell Value is <5 
Format pattern: Green

Condition 3 =Cell value equal to 5 
Format pattern: Yellow

After conditional formatting has been entered and while still in cell C1 Format cell Patterns: Red

That should do the trick.

Pedro


----------



## Mr-Awesome (Jul 29, 2011)

Thanks guys that's helped a lot


----------



## Mr-Awesome (Jul 29, 2011)

Pedro15 said:


> Just played around with some options through google.
> 
> Assume C1 is where you want formatting,
> First go to Custom format and enter
> ...


Haha okay maybe its not solved
what is custom format i assumed you just entered that into the cell and tried to find it on the format menu but couldnt (this is excel 2003 by the way I probably should have mentioned that)
I also tried the second option and that leaves the cell red when it is in 0 as well
However I think I have solved it
I used your formatting method and then altered the setting so when it is less than 0 it goes white and the rest the same with the formatting still as red so then any value over 5 still goes red

It was so simple haha thankyou for helping me


----------



## Pedro15 (Oct 5, 2008)

Mr-Awesome said:


> Haha okay maybe its not solved
> what is custom format i assumed you just entered that into the cell and tried to find it on the format menu but couldnt (this is excel 2003 by the way I probably should have mentioned that)


To custom format go Format/Cells/Number and down the bottom select Custom.

Then under Type enter the format you want. Some are listed otherwise enter your own and go OK.

Good to hear you have sorted it out.

Pedro


----------

