Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting cells with color (Excel 97)

    Hello,

    I'm having trouble formatting the background color of some cells. I need around 20-30 different colors. These colors are all based upon the value within each cell (just integers from 1 to whatever). Conditional formatting is only allowing me three colors, so I don't think I can use that. Is there a function that you can set the background color with? I searched through some older posts and saw stuff about how VBA might need to be used.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting cells with color (Excel 97)

    Chaunce, to give you something workable we'll need more detail about the rules you want to use to set the background color.

    FWIW here's something to get started with, assuming you have a column of numbers and click a cell in that column then run the macro:

    Sub ColorasCellVal()
    Dim rngCell As Range
    For Each rngCell In Selection.EntireColumn.SpecialCells(xlCellTypeCons tants, 23)
    rngCell.Interior.ColorIndex = rngCell.Value Mod 57 ' max number of colors 58
    Next rngCell
    End Sub

    In this case "rngCell.Value Mod 57" returns a remainder number between zero and 57, same as =MOD(cell,57).
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    Well I ran that macro (I think, new to them) and it changed a couple test cells with value 1 to black and with one with value 32 to blue. However, It didn't change the color of the cells I wanted it to. I probably should have mentioned that the values within the cells are retrieved from a different part of the workbook. The code for these cells is like this:
    =IF('Elapsed Time Shift 1'!$D9=3,'Elapsed Time Shift 1'!$B9,"")

    Another thing, Is there a way to not include the first 2 cells in a row? They are the row headers and I would like them to stay how they are.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting cells with color (Excel 97)

    I was anticipating you might have some specific colors for specific numbers, but if not, yes you can use the EntireRow code as you have edited. Note that color zero is No Color (aka No Fill), which may look the same as color 2, white, and that color 1 is black, and with black and several other colors you may not be able to read the cell value due to low contrast.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    Well, The color for each number doesn't matter, as long as there is a detectable difference between them. I guess if it is possible, I would like to avoid the fill colors of black and white. Is that what you mean?

    To change the macro to accomodate rows instead do I just change the
    "For Each rngCell In Selection.EntireColumn.SpecialCells(xlCellTypeCons tants,23)"
    to:
    "For Each rngCell In Selection.EntireRow.SpecialCells(xlCellTypeConstan ts,23)"
    ?

  6. #6
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    I'm sorry that I didn't think of it before, but is there a way to modify the macro to not have black and white be fill colors?

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting cells with color (Excel 97)

    This change will skip No Fill, Black, and White, but you'll still have some woefully unreadable colors!

    rngCell.Interior.ColorIndex = (rngCell.Value + 3) Mod 57

    See my other responses. It might be worhwhile to post a sample of what you want, censored if appropriate.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    That SpecialCells thing worked. The correct cells have now changed color and the headers didn't. Now is there an easy way to avoid black and white?

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting cells with color (Excel 97)

    You type too fast! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting cells with color (Excel 97)

    If all the cells you want to change are derived by formulas, and you want to ignore those that are text or evaluate to "", change the SpecialCells part to:

    SpecialCells(xlCellTypeFormulas, 1)

    What cells did you wish to change background color that didn't change? Can you explain what the criteria is for coloring the background in a bit more detail? If the criteria for coloring is more complex, the coding will need more work.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    Yeah, that worked. I think I'm set now.

    Thank you for being patient with me and also for helping me. If I have anymore questions I'll be back.

  12. #12
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    Is there a way to have the macro auto run over the range of cells?

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formatting cells with color (Excel 97)

    When would you want it to auto-run; on any change to the workbook, or on change of the source data or on close of the workbook, or other?
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    I would use the following code in the Worksheet Change event routine for the worksheet in question:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, ActiveSheet.Range("1:1")) Is Nothing Then
    Exit Sub
    End If
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, ActiveSheet.Range("1:1"))
    If IsNumeric(oCell.Value) Then
    oCell.Interior.ColorIndex = (oCell.Value Mod 53) + 3
    End If
    Next oCell
    Application.EnableEvents = True
    End Sub
    </pre>


    The code above works for all of the cells in the first row of the sheet whose change event routine it is inserted into. To work on other ranges, change the range specified by:

    <pre>ActiveSheet.Range("1:1")
    </pre>


    in the two places where it appears.
    Legare Coleman

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting cells with color (Excel 97)

    If I enter 55 into one of the cells I believe it will fill with black. I think that what you should use is:

    <pre>rngCell.Interior.ColorIndex = (rngCell.Value mod 54) +3
    </pre>

    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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