If Then Conditions

R

Rudy Winter

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
 
F

Frank Kabel

Hi Rudy
post your code :)

--
Regards
Frank Kabel
Frankfurt, Germany

Rudy Winter said:
I am trying to write a VBA code that changes the font color of a date
Click to expand...
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.
 
T

Tom Ogilvy

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 said:
I am trying to write a VBA code that changes the font color of a date if
Click to expand...
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.
 
B

Bob Phillips

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 said:
I am trying to write a VBA code that changes the font color of a date if
Click to expand...
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.
 
R

Rudy Winter

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
 
R

Rudy Winter

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
 
T

Tom Ogilvy

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
 
F

Frank Kabel

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
 
Top