1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Conditional Formatting: Ignore Blank Cells in Conditional formatting

Discussion in 'Business Applications' started by Mr-Awesome, Jul 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Mr-Awesome

    Mr-Awesome Thread Starter

    Joined:
    Jul 29, 2011
    Messages:
    7
    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
     
  2. Enanito01478

    Enanito01478

    Joined:
    Jul 27, 2011
    Messages:
    5
    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.
     
  3. Mr-Awesome

    Mr-Awesome Thread Starter

    Joined:
    Jul 29, 2011
    Messages:
    7
    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
     
  4. Enanito01478

    Enanito01478

    Joined:
    Jul 27, 2011
    Messages:
    5
    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.

    Code:
    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 :)
     
  5. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386
    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
     
  6. Mr-Awesome

    Mr-Awesome Thread Starter

    Joined:
    Jul 29, 2011
    Messages:
    7
    Thanks guys that's helped a lot
     
  7. Mr-Awesome

    Mr-Awesome Thread Starter

    Joined:
    Jul 29, 2011
    Messages:
    7
    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
     
  8. Pedro15

    Pedro15

    Joined:
    Oct 5, 2008
    Messages:
    386

    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
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Conditional Formatting
  1. Couriant
    Replies:
    5
    Views:
    281
  2. zfan11
    Replies:
    1
    Views:
    252
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1009719

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice