Conditional format colors for multiple cells in VBA

Bud · #1 (**permalink**) 04-16-06, 09:25

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

norie · #2 (**permalink**) 04-16-06, 09:58

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

Bud · #3 (**permalink**) 04-17-06, 02:02

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

norie · #4 (**permalink**) 04-17-06, 09:59

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.

Bud · #5 (**permalink**) 04-19-06, 00:28

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

norie · #6 (**permalink**) 04-19-06, 09:08

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.

Dniz · #7 (**permalink**) 04-21-06, 10:46

Bud/norie,

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

Bud · #8 (**permalink**) 04-22-06, 03:13

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??

Dniz · #9 (**permalink**) 04-22-06, 11:17

Check out this line:
Range("A" & I).Interior.ColorIndex

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

Internet Services

Web Development

Digital Marketing

Consumer Tech