Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Peachtree City, Georgia, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Couldn't find an answer with search, so my apologies if this is answered elsewhere already. Is there a simple (or not so simple) way to color cells that are currently unfilled (may or may not have data, but the cells are uncolored)? I have sheets that have several sections already color-coded, but I want to change the color of all of the remaining uncolored cells from white (unfilled) to another more attractive neutral tone, without affecting the cells already colored.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use conditional formatting for this - see the recent thread conditional formatting to detect nulls.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have attached a file for you to view using the =ISBLANK(B2) formula as the conditioning format. Highlight the table of figures and click Format---> Conditonal Formatting to see how it works
    Jerry

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Peachtree City, Georgia, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Jezza View Post
    I have attached a file for you to view using the =ISBLANK(B2) formula as the conditioning format. Highlight the table of figures and click Format---> Conditonal Formatting to see how it works
    I understand (at least I think I do) conditional formatting and the =ISBLANK formula. But my issue is how to make non-colored cells a different color even if they have data already. Using your attachment, how would I make the background color of all the white cells, say, tan without changing the color of the green ones? Actually, I can do that with your sample since it only has two colors and two conditions (blank/not blank). But I have several different sets of data color-coded grey, blue, magenta, red, etc. I just want to change the white ones, and ONLY the white ones to another color. Maybe I didn't explain it well the first time. Or maybe I'm missing the obvious solution. Is there a conditional format formula that says something to the effect of, if cell color is A, make cell color B? Except I don't really want a conditional format; I just want to change an existing format...without changing all the others. And without doing it manually. Really more like a find/replace scenario. Tried that too, but can't figure out a way to find based only on cell color.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Got you...I think, so will it be numerical values that will be inputted?

    1 < X <10 Grey
    10< X<20 Blue
    20<X<30 Green
    30<X<40 Yellow

    etc
    Jerry

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Add this code to the workbook section of your file in VBE



    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set RangeToFormat = Sheets("Sheet1").Range("B2:G4")
    For Each cell In RangeToFormat
    With cell
    ' Empty cells
    'If IsEmpty(cell) Then
    '.Interior.ColorIndex = xlNone
    ' Numeric cells
    If IsNumeric(cell.Value) Then
    Select Case cell.Value
    Case 1 To 10
    .Interior.ColorIndex = 38
    Case 11 To 20
    .Interior.ColorIndex = 40
    Case 21 To 30
    .Interior.ColorIndex = 36
    Case 31 To 40
    .Interior.ColorIndex = 35
    Case 41 To 50
    .Interior.ColorIndex = 34
    End Select
    ' Error cells
    ElseIf IsError(cell.Value) Then

    'Error cells
    .Interior.Color = 3
    ' Other cells (text)
    Else
    .Interior.ColorIndex = xlNone
    End If
    End With
    Next cell
    End Sub
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •