12 conditional formats on dates

[Eileen]

Hi,

I've got the code from a previous post for doing more than
3 conditional formats and it works fine for numbers.
However, I need to use it for the month value in a date
and can't extract the month value.
Here's the code so far that looks just for number values:

Private Sub Worksheet_Calculate()
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant

Set vRngInput = Range("AV4:AV15")

If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput

'determine the colour
Select Case rng.Value
Case Is = 1: Num = 10 'green
Case Is = 2: Num = 7 'magenta
Case Is = 3: Num = 46 'orange
'etc

End Select

'apply the colour
rng.Interior.ColorIndex = Num

Next rng

End Sub

Can someone please tell me how to do the same thing but
look at the month when there are dates in the cells
AV4:AV15?

 

[Greg Koppel]

=Month(cell with date value)

HTH, Greg

 

[Anders S]

Hi Eileen,

Try this,

----

Private Sub Worksheet_Calculate()
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant

Set vRngInput = Range("AV4:AV15")

If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput

'determine the colour
Select Case Month(rng.Value)
Case Is = 1: Num = 10 'green
Case Is = 2: Num = 7 'magenta
Case Is = 3: Num = 46 'orange
'etc

End Select

'apply the colour
rng.Interior.ColorIndex = Num

Next rng

End Sub