Expand on current VBA procedure

[pbt]

Hi folks,

I have the follwing code that will turn the ajoining cell to a particular color based on the Month of a date that is formated 1/12/06.

I need to expand on this code so it will also look at the Year. This spreedsheet will run into the next year so just looking at the month will not do. Of course I would change the color index for the next year to something else.

Sub ColorByMonth()
    Dim rCell As Range
    Application.ScreenUpdating = False
    Application.Calculate
    On Error GoTo Xit
    For Each rCell In Range("j2:j" & Range("j65536").End(xlUp).Row)
        Select Case Month(rCell)
            Case 1
                rCell.Offset(0, 1).Interior.ColorIndex = 36
            Case 2
                rCell.Offset(0, 1).Interior.ColorIndex = 42
            Case 3
                rCell.Offset(0, 1).Interior.ColorIndex = 39

This continues for all twelve months (Case 12)

Any help would be greatly appreciated.

Thanks
h.h.

 

[pbt]

Yes, I did toggle other sheets before going back to subject sheet and running code.

 

[jindon]

OK
Can you change the line to

Private MyColor2006(), MyColorElse()

 

[pbt]

Same thing ~ no change for the other two columns

 

[pbt]

Same thing ~ no change for the other two columns

 

[jindon]

Can you just add the following code and run?

Sub ArrayCheck()
For Each e In MyColor2006
   MsgBox e
Next
End Sub

Again you need to toglle the sheet once before you run the code

 

[pbt]

Ran the Sub ArrayCheck().

Message box gave me all the numbers in the array each time I clicked OK: (7, 13, 16, 19, 23, 27, 30, 35, 39, 41, 44, 47)

Also did the check on, For Each e In myColorElse:
got (8, 14, 17, 20, 24, 28, 31, 36, 40, 42, 45, 48) with each click

h.h.

 

[pbt]

bump

Good afternoon to you ( 9:30 PM Thu for me) Jindon (I see you are on line)

Hopeing you have seen my last post.

Macro still not working.

h.h.

 

[jindon]

Hi

I've just found my typo in the code

check the word "ColorIndex"

one is ColoeIndex and the other one is "ColoIndex"

Those should read as ColorIndex

 

[pbt]

I did find that and already made the corrections prior to my last test

 

[jindon]

OK then

We need to move on with another method... wait