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.

 

[jindon]

Hi
Try something like

Dim myColor As Integer
For Each rCell In Range(.........
    Select Case Month(rCell)
        Case 1 : myColor = 36
        Case 2 : myColor = 42
        Case 3 : myColor = 39
        .....
        .....
     End Select
     myColor = myColor + Year(Date) - Year(rCell)
     rCell.Offset(, 1).Interior.ColorIndex = myColor
Next

You need to adjust to avoid duplicate colors though.....

 

[pbt]

Thanks for the quick reply Jindon

I tried your suggestion. This is how i input it into my code;

Sub ColorByMonth()
    Dim rCell As Range
    Dim myColor As Integer
    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: myColor = 36
               ' rCell.Offset(0, 1).Interior.ColorIndex = 36
            Case 2: myColor = 42
                'rCell.Offset(0, 1).Interior.ColorIndex = 42
            Case 3: myColor = 39
               ' rCell.Offset(0, 1).Interior.ColorIndex = 39
                ................
                ................
              If rCell.Value = ("") Then
    rCell.Offset(0, 1).Interior.ColorIndex = 0
End If
        End Select
        myColor = myColor + Year(Date) - Year(rCell)
     rCell.Offset(, 1).Interior.ColorIndex = myColor
    Next

I left my original lines in (disabled) just to go through testing.

I did not "adjust" as you suggested, yet - ran a test to see what happens.

I changed the date to a future Year and it changes the color at will when I run the macro.
Meaning , I dont' know where it got the index number to change the cell for the Year 2007 or 2005. It is actually putting in some "custom" colors, not from the standard pallette.

h.h.

 

[jindon]

ColorIndex is from 0 to 56, I think it was.

Therefore, it should include colours not in the pallette.

Something wrong with it?

Or do you want to assign same colour to different month?

 

[jindon]

You can check the colour vs Color Index

Sub TestColours()
Dim i As Integer
    For i = 0 To 55
        Cells(i + 1, "a").Interior.ColorIndex = i 
        Cells(i + 1, "b") = i
    Next
End Sub

 

[pbt]

Sorry, you are right.

ColorIndex is from 0 to 56, I think it was.

I was just looking at the colors in the drop down and not thinking.

What I am trying to do is if Jan 2006 is in the range and color index is 36, that's okay. But if Jan 2007 is in the range I want the cell to change to a different color. Same goes for if Jan 2008 is there.

I just need a little help in figuring this out. I can assign the numbers once I have one of the other dates figured out.

Thanks
h.h.

EDIT:
Like I said in my previou post. I don't know where it getting this other color from based on the date change. And will this be constant.

 

[jindon]

myColor = myColor + Year(Date) - Year(rCell)

This line determines myColor(ColorIndex) to be assinged.

Say you we have Jan/2006
1st, Select Case statement selects, Case 1 which is myColor = 36
2nd, adjust myColor with the Year of eCell against Current year with above line.

so myColor(36) = myColor(36) + Yeat(Date)=2006 - Year(rCell) = 2006

 

[pbt]

Thanks for being so patient with me on this, Jindon

I am having a hard time understanding the following line:

so myColor(36) = myColor(36) + Yeat(Date)=2006 - Year(rCell) = 2006

What I would like to do is choose my color for the other Years, because I have a UDF assigned to a cell on another worksheet that will Sum all the cells with that particular index number. Example: =SumByColor(WEBB!$K$2:$K$2000,18,FALSE)

So in this example I would have to change the "18" to whatever I assigned to the year 2007.

Your first reply changes the colors of the cells if I change the date. But like I said it puts colors in there that I do not know what the index numbers are unless I insert a row somwhere and run the code you suggested to find out. "Seems like alot of work just to find out the index number."

I do have a list of all 56 colors and index numbers provided by erikvon geit so I know which colors I want for the other years.

I hope this explains a little better.

Thanks
h.h.

 

[jindon]

We have assinged 36 to 1 (Jan)

According to that line
if Jan 2007 then
myColor = myColor(36) + Year(Date) = 2006 - Year(rCell) = 2007
Therefore myColor = 35

This means ColorIndex for Jan 2007 is 1 less than that of Jan 2006

or do you want to assing any particular colorIndex to Jan 2007?

 

[pbt]

Yes, I would like to assign my own number to Jan 2007.