I am trying to write a VBA code that changes the font color of a date if it is a specific month. So far I have been using the If Then function, but everytime I run the macro it changes to the first color in the VBA code and not a different color for every month

Can anyone help me

Thank you

Rudy

Hi Rudy
post your code :-)

--
Regards
Frank Kabel
Frankfurt, Germany

"Rudy Winter" schrieb im
Newsbeitrag ...
I am trying to write a VBA code that changes the font color of a date
if it is a specific month. So far I have been using the If Then
function, but everytime I run the macro it changes to the first color
in the VBA code and not a different color for every month.

Can anyone help me?

Thank you,

Rudy

This is the Macro I used

If Date = 1 / 1 / 4 <= 1 / 31 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
If Date = 2 / 1 / 4 <= 2 / 29 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
If Date = 3 / 1 / 4 <= 3 / 31 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
If Date = 4 / 1 / 4 <= 4 / 30 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
End Sub

Hi Rudy
try the following:
Sub foo()
With Range("A1:G40").Font
Select Case Date
Case DateSerial(2004, 1, 1) To DateSerial(2004, 1, 31)
.ColorIndex = 3
Case DateSerial(2004, 2, 1) To DateSerial(2004, 2, 29)
.ColorIndex = 4
Case DateSerial(2004, 3, 1) To DateSerial(2004, 3, 31)
.ColorIndex = 5
Case DateSerial(2004, 4, 1) To DateSerial(2004, 4, 30)
.ColorIndex = 6
Case DateSerial(2004, 1, 1) To DateSerial(2004, 3, 31)
.ColorIndex = 3
End Select
End With
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany


Rudy Winter wrote:
This is the Macro I used:

If Date = 1 / 1 / 4 <= 1 / 31 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 3
End If
If Date = 2 / 1 / 4 <= 2 / 29 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 4
End If
If Date = 3 / 1 / 4 <= 3 / 31 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 5
End If
If Date = 4 / 1 / 4 <= 4 / 30 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 6
End If
End Sub

Generally, the month function will see a blank cell or a cell with a zero as
January. This might be the root of your problem.

You need to check and make sure the cell isn't blank.

--
Regards,
Tom Ogilvy

"Rudy Winter" wrote in message
...
I am trying to write a VBA code that changes the font color of a date if
it is a specific month. So far I have been using the If Then function, but
everytime I run the macro it changes to the first color in the VBA code and
not a different color for every month.

Can anyone help me?

Thank you,

Rudy

This is the macro I used

If Date = 1 / 1 / 4 <= 1 / 31 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
If Date = 2 / 1 / 4 <= 2 / 29 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
If Date = 3 / 1 / 4 <= 3 / 31 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
If Date = 4 / 1 / 4 <= 4 / 30 / 4 The
Range("A1:G40").Selec
Selection.Font.ColorIndex =
End I
End Sub

Excel sees 1/1/4 as 1 divided by 1 = 1, then that 1 divided by 4 = 0.24

A date constant in Excel is expressed as

#1/1/04#

or
#1/1/2004#

also you have to test each condition separately

If Date = #1/1/2004# and Date <= #1/31/2004# then

however, it is easier to do

if month(date) = 1 then

if you are using a sequential pattern

Range("A1:G40").Interior.ColorIndex = month(Date) + 2



--
Regards,
Tom Ogilvy

"Rudy Winter" wrote in message
...
This is the macro I used:

If Date = 1 / 1 / 4 <= 1 / 31 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 3
End If
If Date = 2 / 1 / 4 <= 2 / 29 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 4
End If
If Date = 3 / 1 / 4 <= 3 / 31 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 5
End If
If Date = 4 / 1 / 4 <= 4 / 30 / 4 Then
Range("A1:G40").Select
Selection.Font.ColorIndex = 6
End If
End Sub

It would be simpler to use conditional formatting.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rudy Winter" wrote in message
...
I am trying to write a VBA code that changes the font color of a date if
it is a specific month. So far I have been using the If Then function, but
everytime I run the macro it changes to the first color in the VBA code and
not a different color for every month.

Can anyone help me?

Thank you,

Rudy