Search code examples
excelvbafor-loopcolors

Changing Interior Colour of Cells In For Each Loop

1]1Sub ColourChange()

Set WS = Sheets("Days")
Set WC = Sheets("Runs")

Dim pr As Long
Dim rr As Long
Dim hr As Long
Dim CurrRow As Long
Dim PrevRow As Long
Dim CurrColor As Long
Dim ColorArr As Variant
Dim ColorIndex As Integer
Dim ColorRange As Range
Dim Color0 As Long
Dim Color1 As Long
Dim Color2 As Long
Dim Color3 As Long
Dim Color4 As Long
Dim Color5 As Long
Dim Color6 As Long
Dim Color7 As Long
Dim Color8 As Long
Dim Color9 As Long
Dim Color10 As Long
Dim Color11 As Long
Dim tms As Long

ColorArr = Array(Color0, Color1, Color2, Color3, Color4, Color5, Color6, Color7, Color8, Color9, Color10, Color11)
ColorIndex = 0
    Color0 = RGB(33, 139, 130)
    Color1 = RGB(154, 217, 219)
    Color2 = RGB(229, 219, 217)
    Color3 = RGB(152, 212, 187)
    Color4 = RGB(235, 150, 170)
    Color5 = RGB(106, 76, 147)


pr = WC.Range("A" & Rows.Count).End(xlUp).Row + 13
Debug.Print pr
Dim TabTimes As Range
Set TabTimes = Application.Range("Days!B15:B" & pr)
TabTimes.Select

tms = pr + 3
Debug.Print tms
pr = WC.Range("H" & Rows.Count).End(xlUp).Row
pr = pr + tms - 1
Debug.Print pr
Dim CPTTimes As Range
Set CPTTimes = Application.Range("Days!B" & tms & ":B" & pr)

For Each cel In TabTimes.Cells
If cel.Interior.Color <> RGB(166, 166, 166) Then
cel.Interior.Color = ColorArr(ColorIndex)
ColorIndex = ColorIndex + 1

        End If
Next cel
On Error Resume Next
End Sub

Afternoon people :) I'm currently trying to set the color of some cells based on a predetermined array. The cells are in the B column starting at Cell B15 and the For Each statement loops through them. As you can see by the code, I want the different times to have the preset colors (Color1/Color2) because I have another code which changes the RGB values so the people using the spreadsheet can choose their own color scheme, if that makes sense.

Ignoring my poor coding in setting the named ranges, is there anyway i can get this to work? Currently it just changes all the cell interior colours to black and changes the last one in the range to one of the colors... I suspect a For Each isn't the best way to deal with this problem

Anyone have any ideas?

enter image description here

Solution

  • On the following line you declare the array to have 12 items, all with the color value black (=0), because those Color0 ... Color11 are not yet assigned any color values.

    ColorArr = Array(Color0, Color1, Color2, Color3, Color4, Color5, Color6, Color7, Color8, Color9, Color10, Color11)

    Then, you assign values to some of the color constants, but those values do not magically move into the array

        ColorIndex = 0
    Color0 = RGB(33, 139, 130)
    Color1 = RGB(154, 217, 219)
    Color2 = RGB(229, 219, 217)
    Color3 = RGB(152, 212, 187)
    Color4 = RGB(235, 150, 170)
    Color5 = RGB(106, 76, 147)

    Also, you are missing color definitions for Color6 ... Color11

    Move the assignment of the color constants to the array ColorArr, to after you have assigned values to all the ColorXX constants.