Change color of cell when entering month

Closed
flash - Jan 20, 2010 at 03:49 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 14, 2010 at 05:00 PM
Hello,
I am trying to make a spreadsheet that when I enter a month in a column I want it to automatically change the color of the font or cell,
example:
january turn to red
february turn to blue
march turn to green
I need to have a different color for every month, I am only familar with the excel basics so I would need a step by step.
Can anyone help.

Thanks

2 replies

thats easy
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 28, 2010 at 11:23 AM
easy is relative term.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Apr 28, 2010 at 11:27 AM
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 = CInt(Target)
    '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
Is there a way of doing the same thing but instead of entering a month such as March or April, you enter in a specific date such as 6/1/10? If so, would you be as so kind to let me know what code I could cut and past into my Project Tracking worksheet?

This would be very helpful.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jun 14, 2010 at 05:00 PM
What do you mean by "specific date".
0