how do I put today's date and other dates in macro condition?

[Guest]

I have the following macro where I have dates embedded in certain cells
because I do not know how to put them in a condition statement. In the macro
below cell $R$1 is Today(), cell $S$1 is 01/01/00 and cell $T$1 is Today() +
7. How can I make the macro work with refering to the values in the cells?


Sub todaydate()


Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$S$1", Formula2:="=$R$1"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With
Selection.FormatConditions(1).Interior.ColorIndex = 36
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$R$1", Formula2:="=$T$1"
Selection.FormatConditions(2).Font.ColorIndex = 41
Selection.FormatConditions(2).Interior.ColorIndex = 36
Range("A1").Select

End Sub

 

[Guest]

You need: Date (VBA's way of accessing today's date) and #1/1/2000# (hashes
to delimit dates in code).

Also useful are Time and Format, eg:
Format(Date,"dd/mm/yyyy")

 

[Bob Phillips]

Try this

Sub todaydate()

With Columns("F:F")
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=01/02/2000", _
Formula2:="=" & Format(Date, "dd/mm/yyyy")
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With
.FormatConditions(1).Interior.ColorIndex = 36
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=" & Format(Date, "dd/mm/yyyy"), _
Formula2:="=" & Format(Date + 7, "dd/mm/yyyy")
.FormatConditions(2).Font.ColorIndex = 41
.FormatConditions(2).Interior.ColorIndex = 36
End With

End Sub

you might need to play with the date formats

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,

It is not working. I tried changing the date format to mm/dd/yyyy,
yyyy/mm/dd. What is actually happening is it puts the date in this format
4-4-2006 in the conditional formating. I am not sure it recognizes it as a
date. How do I get the date with the slash / instead of the dash -?

 

[Bob Phillips]

That is a valid date format.

I think the important thing is to force a date. Try this version

Sub todaydate()

With Columns("F:F")
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=--""2000-01-01""", _
Formula2:="=--""" & Format(Date, "yyyy-mm-dd")
& """"
With .FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With
.FormatConditions(1).Interior.ColorIndex = 36
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=--""" & Format(Date, "yyyy-mm-dd")
& """" _
Formula2:="=--""" & Format(Date+7,
"yyyy-mm-dd") & """"
.FormatConditions(2).Font.ColorIndex = 41
.FormatConditions(2).Interior.ColorIndex = 36
End With

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)