12 Conditional Formats based on Dates

[Eileen]

Hi,

I picked up a previous post on how to do multiple
conditional formats based on the value in a cell.
However, I need to change it to be conditional on the
month part of a date, not just a numeric value.

Here is the code I've got so far which works fine for
numeric 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

End Select

'apply the colour
rng.Interior.ColorIndex = Num

Next rng

End Sub

The range of AV4:AV15 contains dates (dd/mm/yyyy) and I
need to do the formatting based on the month, eg if 02
(february), then green etc.

Can someone advise on how to change the code to do this?
Thanks

 

[Dave Peterson]

Select Case rng.Value
could become
Select Case month(rng.Value)

You may even want to check for a date:

for each rng in vRngInput.cells
if isdate(rng.value) = false then
'do nothing
else
select case month(rng.value)
....
end if
next rng

 

[Eileen]

Thanks Dave - it worked!

-----Original Message-----
Select Case rng.Value
could become
Select Case month(rng.Value)

You may even want to check for a date:

for each rng in vRngInput.cells
if isdate(rng.value) = false then
'do nothing
else
select case month(rng.value)
....
end if
next rng





--

Dave Peterson
(e-mail address removed)
.