Previous Topic Next Topic

Change color of cell when entering month

Closed
flash - Jan 20, 2010 at 03:49 PM
rizvisa1 Posts 4478 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
Related:

2 responses

Answer 1 / 2
Kapil
Apr 28, 2010 at 02:27 AM
thats easy
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 28, 2010 at 11:23 AM
easy is relative term.
Answer 2 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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
Vince
Jun 14, 2010 at 03:53 PM
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.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 14, 2010 at 05:00 PM
What do you mean by "specific date".