Previous Topic Next Topic

Different color for Different month

Closed
Kapil - Apr 28, 2010 at 01:16 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 28, 2010 at 12:11 PM
My req is relatively simple.

Pls tell me how I can assign a different color for different month.

Example:

I enter 12 jan 2010 - color should be (say) red
I enter 23 Mar 2010 - color should be (say) yellow
etc
etc
etc

So .. 1 unique color for each month

Just lookin for a simple code.... conditional formatting cant work cuz restriction of max 3 conditions

Thanks


Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 28, 2010 at 12:11 PM
You would need to use macro to do it. Presuming you are working in column A

Press ALT + F11 to open VBE

in the vbe, double click on the sheet that you are working on

paste this code 


Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Variant
Dim myColor As Variant
Dim myMonth As Integer

    'get out of the change is not in the column 1 or A
    If Target.Column <> 1 Then Exit Sub
    
'"red" '3
'"blue" '32
'"yello" '6
'"green" '10
'"lime" '4
'"pink" '26
'"orange" '46
'"brown" '9
'"purple" '17
'"white" '2
'"black" '1
'"grey" '15

    ' color array
    iColor = Array(3, 32, 6, 10, 4, 26, 46, 9, 17, 2, 1, 15)
    
    'default have not color
    myColor = xlNone
    
    'my month
    myMonth = Format(Target, "m")
    'if month is between 1-12 then get color for that month
    If (myMonth >= 1 And myMonth <= 12) Then
        myColor = iColor(myMonth - 1)
    End If
    
    Target.Interior.ColorIndex = myColor

End Sub
0