Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking Unanswered: Conditional format colors for multiple cells in VBA

    Good morning,

    "Happy Easter" to you all. Me, I am here at work.
    Question:
    How can I code a vast range of cells to change to one of three colors in VBA? I have don't it with the Conditional Format tool, but interested in an easy way to do it in Code, and to go possibly beyond the standard 4 options. Also, where can you find the Color Index for the colors you want to use? Meaning the number for all of the available colors. You help in this would be so greatly appreciated.

    thank you and have a nice holiday,
    BUD

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Bud

    Do you want to do this for existing data or data that will be getting changed/added?

    If it's for changed/added data you could use the worksheet change event.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColor As Long
        Select Case Target.Value
            Case "Value1"
                iColor = 3
            Case "Value2"
                iColor = 4
            Case "Value3"
                iColor = 5
            Case "Value4"
                iColor = 6
            Case "Value5"
                iColor = 7
        End Select
        
        Target.Interior.ColorIndex = iColor
    End Sub
    To find out the colours for the color index try this, or check out Help.
    Code:
    Sub test()
    Dim I As Long
        For I = 1 To 56
            Range("A" & I).Interior.ColorIndex = I
            Range("B" & I) = I
        Next I
    End Sub

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Red face

    Quote Originally Posted by norie
    If it's for changed/added data you could use the worksheet change event.
    [code]
    Thanks Norie,

    Yes, I wanted the code for data to be added/changed to the spreadsheet. I copied your code and tried to see how it worked but didn't know just how to apply it. What will happen is when the user inputs "Y", the backcolor for that cell should change to Green (Index 50). "U" = Yellow (Index 6), "X" = Red (Index 3) and finally "N/A" = Gray (Index 48). All will be used in different locations but input without the " " (quotes). Below is what I did to alter the code to try to make it work. Guess I still need your help.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColor As Long
        Select Case Target.Value
            Case "Y"
                iColor = 50
            Case "U"
                iColor = 6
            Case "X"
                iColor = 3
            Case "N/A"
                iColor = 48
        End Select
        
        Target.Interior.ColorIndex = iColor
        
    End Sub
    
    Sub test()
    Dim I As Long
        For I = 1 To 56
            Range("A" & I).Interior.ColorIndex
            Range("B" & I) = I
        Next I
            
    End Sub
    Note, I am not all that good in Excel and still trying to make things work. I appreciate your time and assistance.
    BUD

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    BUD

    Where did you put the code?

    The first set of code should go in a worksheet module, which is accessible by right clicking the worksheet tab and selecting View Code.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by norie
    BUD

    Where did you put the code?

    The first set of code should go in a worksheet module, which is accessible by right clicking the worksheet tab and selecting View Code.
    Thanks so much Norie,
    I placed the code where you said and it worked well. Not sure though about the Sub Test thingy. How do I use that and where do I place it?

    thanks so very much,
    BUD

  6. #6
    Join Date
    Mar 2006
    Posts
    163
    BUD

    The test sub is seperate code.

    If you put it in a standard module (Insert>Module) and run it you should get the cells in column A coloured with the cells in column B containing the corresponding ColorIndex for the colour in A.

  7. #7
    Join Date
    Oct 2004
    Posts
    26
    Bud/norie,

    The code can reside in the same worksheet in which you are calling Worksheet_Change(). It works beautifully.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by norie
    BUD

    The test sub is seperate code.

    If you put it in a standard module (Insert>Module) and run it you should get the cells in column A coloured with the cells in column B containing the corresponding ColorIndex for the colour in A.
    Norie,

    I inserted this in a Module and did Run and got an error:

    Compile Error: Invalid use of property
    and has this highlighted: .ColorIndex

    what am I doing wrong??

  9. #9
    Join Date
    Oct 2004
    Posts
    26
    Check out this line:
    Range("A" & I).Interior.ColorIndex

    It should be :
    Range("A" & I).Interior.ColorIndex = I

Posting Permissions

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