Thread: How do I set a date range for conditional formatting in a macro?

I am looking to write a macro that will check for a date range in a group of
cells (for example: today through one year ago today) in a worksheet. Then
I would like to highlight the cells that fall within this date range in bold
red. So far, all I get are syntax errors when trying to write this macro.
Any ideas?

Assuming dates are in col. A, select col. A., go to
Format > Conditional Formatting, Formula Is and put:

=AND($A1>=TODAY()-365,$A1<TODAY())

Press the Format radio button and format as desired.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I am looking to write a macro that will check for a date
range in a group of
>cells (for example: today through one year ago today)
in a worksheet. Then
>I would like to highlight the cells that fall within
this date range in bold
>red. So far, all I get are syntax errors when trying to
write this macro.
>Any ideas?
>.
>

hi,
a much easier way would be to use conditional formatting
but here's code that would work.
this assumes that the dates are in column A.
Sub macmarkdate()
Dim lastrow As Long
Dim g As Range
Dim cell As Range
Application.ScreenUpdating = False
Set g = Range(Range("A1"), Range("A1").End(xlDown))
For Each cell In g
If cell.Value < now()-365 Then
cell.Interior.ColorIndex = 46
End If
Next cell

Application.ScreenUpdating = True

End Sub

>-----Original Message-----
>I am looking to write a macro that will check for a date
range in a group of
>cells (for example: today through one year ago today) in
a worksheet. Then
>I would like to highlight the cells that fall within this
date range in bold
>red. So far, all I get are syntax errors when trying to
write this macro.
>Any ideas?
>.
>

thanks for the tip! but now it's lead to another issue, in that even the
blank cells are highlighted. how do i specify not to format a blank cell?
also, where do you find the colour index numbers? i see that you have red =
46, but is there a listing somewhere that correlates numbers to colours?

"anonymous@discussions.microsoft.com" wrote:

> hi,
> a much easier way would be to use conditional formatting
> but here's code that would work.
> this assumes that the dates are in column A.
> Sub macmarkdate()
> Dim lastrow As Long
> Dim g As Range
> Dim cell As Range
> Application.ScreenUpdating = False
> Set g = Range(Range("A1"), Range("A1").End(xlDown))
> For Each cell In g
> If cell.Value < now()-365 Then
> cell.Interior.ColorIndex = 46
> End If
> Next cell
>
> Application.ScreenUpdating = True
>
> End Sub
>
> >-----Original Message-----
> >I am looking to write a macro that will check for a date
> range in a group of
> >cells (for example: today through one year ago today) in
> a worksheet. Then
> >I would like to highlight the cells that fall within this
> date range in bold
> >red. So far, all I get are syntax errors when trying to
> write this macro.
> >Any ideas?
> >.
> >
>