Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting Background Color (Excel 2000 SR-1)

    I am trying to find a way, either with a VBA routine, or function, or some other method, to change the background color of a cell based on another cells value. I would even use a color index that matches the color palette numbers and am trying to use the RGB codes, but being a novice with VBA, I can't seem to "make" this work automatically, or programmatically.

    Specifically, I am reconciling a bank statement with deposits from about 20 different locations. I want to color code each locations deposit by a different color, the sum the totals by those colors. I can set the color manually, of course, but was trying to find a way to do this automatically.

    I can't do this with conditional formatting because I have about 20 different colors I am using. When I set the colors, I then run a SumByColor function to obtain the values of each of the cells by colors. Again, having trouble automating this.

    Any help is appreciated.
    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting Background Color (Excel 2000 SR-1)

    If you want to set the background colors, you can use either ColorIndex (1, 2, ...) of Color (RGB values). But Excel only displays a limited range of colors.

    Assuming that the locations are in column A, you could have code like this (using RGB values). Replace "A", "B" etc. by the names of the locations.

    Sub SetColors()
    Dim rngDeposits As Range
    Dim rngRow As Range
    Set rngDeposits = Range("A1").CurrentRegion
    For Each rngRow In rngDeposits.Rows
    Select Case rngRow.Cells(1, 1).Value
    Case "A"
    rngRow.Interior.Color = RGB(255, 255, 192)
    Case "B"
    rngRow.Interior.Color = RGB(192, 192, 255)
    Case "C"
    rngRow.Interior.Color = RGB(224, 255, 255)
    Case "D"
    rngRow.Interior.Color = RGB(255, 255, 128)
    Case "E"
    rngRow.Interior.Color = RGB(128, 128, 255)
    Case "F"
    rngRow.Interior.Color = RGB(128, 0, 255)
    Case "G"
    rngRow.Interior.Color = RGB(255, 192, 192)
    End Select
    Next rngRow
    End Sub

    To sum the deposits by location, I wouldn't use the colors, but create a pivot table. A pivot table uses the data directly, instead of relying on the color.

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

    Re: Setting Background Color (Excel 2000 SR-1)

    Steve <!post=here,170001>here<!/post> and <!post=here,165619>here<!/post> are a couple of threads on VBA color coding that may be helpful. Have a further look for threads in the Excel Forum using Interior.ColorIndex, Offset and Select Case, this latter being the way you can set multiple colors according to each condition. If you can provide more details of your challenge, someone can probably write it all for you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Background Color (Excel 2000 SR-1)

    Hans,
    Thanks for this reply and I will give a shot to see if I can get it to work--its sounds straightforward enough, the question is more about my VBA skills :-).
    I could use a pivot table and may do so in the future. The color coding provides a more visual representation of both what cleared the bank from last month and what has yet to clear--you scroll down the page and see that clear white background cell and you know something is up. It also helps to see the various locations' deposits, especially as I have to resort the listing of deposits, etc--there are often a few hundred of these.
    And once I started working on the color thing--I want to figure it out for fun--a future use.
    Will let you know how things come out.
    Thanks again,
    Steve

  5. #5
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Background Color (Excel 2000 SR-1)

    I will give what Hans suggested a try and I also copied some ideas from the posts you gave as hints. Let me see if I can put in English what I will try and do through code:
    Name a range of data (probably will be one-column wide because I am not sure how to make a cell a certain color based on the data in another column/cell).
    Process through each cell in the range and process through a series of 'tests' via a SelectCase thing (method,?)
    After the program executes, all similar items in the range have the same color. Can't use conditional formatting because I will have between 25-35 "conditions."

    Will give it a shot.
    Steve

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

    Re: Setting Background Color (Excel 2000 SR-1)

    BTW, it would be helpful if you mentioned what kind of source data triggers the color, as in, is it text or a value, and whichever, is it an account or what. That way someone can help you better with the

    Case "Something"

    part of the code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Setting Background Color (Excel 2000 SR-1)

    <hr>I am not sure how to make a cell a certain color based on the data in another column/cell<hr>
    Steve, that's where you use cell.Offset(Row, Column). You can work from the cell being examined to the offset cell to be modified, or vice versa. Using Hans' code, here are some simple untested modifications:

    Sub SetColors()
    Dim rngDeposits As Range
    Dim rngCell As Range
    Set rngDeposits = Intersect(Columns("A:A"), WorkSheet("Name").UsedRange)
    For Each rngCell in rngDeposits
    Select Case rngCell.Value
    With rngCell.Offset(0,4) ' a 4 column offset, so the same-row cell in column D is colored
    Case "A"
    .Interior.ColorIndex = 2
    Case "B"
    .Interior.ColorIndex = 3
    Case "C"
    ' <etc, etc>
    End With
    End Select
    Next rngCell
    Set rngDeposits = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Setting Background Color (Excel 2000 SR-1)

    1 suggestion fro getting the sums of the different "types". Instead of using a custom function to sum by Color, since the colors are based on some other citeria, you can sum your "types" using the "source criteria" rather than the color.

    With this way, you can use built-in functions which are much faster than custom function.

    Steve

  9. #9
    Lounger
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Background Color (Excel 2000 SR-1)

    John,
    Thanks for the guidance--I am working on this code even as your email arrived. Presently, I am stuck on something simple--I see where if I change the cell's interior color to something like dark blue, the std black font becomes illegible. So I was simply trying to add code to change the font color to yellow. I am stuck but perusing my books. What I have tried is something like:

    For Each rngRow In rngDeposits.Rows
    Select Case rngRow.Cells(1, 1).Value
    Case "CBO Deposits"
    rngRow.Interior.Color = RGB(255, 255, 0)
    Case "Gulf Breeze CC"
    rngRow.Interior.Color = RGB(0, 255, 0)
    Case "BMP Site"
    rngRow.Interior.ColorIndex = 5 'testing the use of a more legible color index number -it worked!
    rngRow.Font.Color = 6 ' this doesn't work

    maybe this should be
    rngRow.Font.ColorIndex = 6

    Wondering if this is the most efficient way to change both the interior color and the font color-

    I am using text in the Case statement i.e., Case = "CBO Deposits"

  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: Setting Background Color (Excel 2000 SR-1)

    rngCell.Font.ColorIndex = 3

    should work.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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