How can i change cell colour depending on month of date in cell?

  • Thread starter Thread starter andy75
  • Start date Start date
A

andy75

I have a column with a range of dates corresponding to when an item was
tested and would like to automatically change the colour depending o
which month it was tested. Jan-Mar = red, Apr-Jun= green, Jul-Sep
blue and Oct-Dec= yellow. No date = no colour. Anyone have an
suggestions? Do I need to run a macro or is there a simpler way?

Thanks

And
 
Here's a bit of code that might work (here, I have all dates in column A):

Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

Dim i As Integer
Dim iColor As Integer

i = 0
iColor = 0

i = Month(Target.Value)

If Not Intersect(Target, Range("a:a")) Is Nothing Then
Select Case i
Case 1 To 3
iColor = 3
Case 4 To 6
iColor = 10
Case 7 To 9
iColor = 11
Case 10 To 12
iColor = 6
Case Else
End Select

Target.Font.ColorIndex = iColor

End If
End Sub
 
Hi!

If you want to change the cell *background color* then you will need an
event macro to do this. You can use conditional formatting (no VBA code
required) for up to 3 conditions but if you want the *background color* to
change then you have 4 conditions.

If you want the *text color* to change then you can use cf. You still have 4
conditions but you can set one of those to be the default leaving you with
the 3 to conditionally format.

Assume your dates are in the range A1:A10.
Set the default font color for that range to RED.

Now, apply conditional formatting:

Select the range A1:A10
Goto Format>Conditional Formatting
Condition 1
Formula is: =AND(MONTH(A1)>=4,MONTH(A1)<=6)
Click the Format button
Set the font color to GREEN
OK
Click the Add button
Condition 2
Formula is: =AND(MONTH(A1)>=7,MONTH(A1)<=9)
Click the Format button
Set the font color to BLUE
OK
Click the Add button
Condition 3
Formula is: =AND(MONTH(A1)>=10,MONTH(A1)<=12)
Click the Format button
Set the font color to YELLOW
OK out

Now, as you type dates in the range they will appear red but as soon as you
hit the enter key they will change to the appropriate color.

Note: that YELLOW font is pretty hard to see on a white background! Maybe
you can experiement with your color choices even applying bolding if
desired.

Biff
 
Back
Top