VBA for Dates and Row Colors

[Dave Y]

Hello,

I need help writing the VBA code to acomplish the
following: I need to find any date in a spreadsheet that
is <= 90 days old; then I want to change the back color of
the row to yellow for those dates. For example if I had
the following data:
Name Acct# AcctCloseDate
This Guy 123456 01/01/2004
That Guy 654321 10/15/2004
Since the first date is over 10 months old (way past 90
days) I would leave the row as it is. Since the second
date is only 6 days old I would want to change the whole
row or at least the cell contaning that date to yellow.
Any help with a solution will be greatly appreciated.
Thank you.

Dave Y

 

[Ron Rosenfeld]

Hello,

I need help writing the VBA code to acomplish the
following: I need to find any date in a spreadsheet that
is <= 90 days old; then I want to change the back color of
the row to yellow for those dates. For example if I had
the following data:
Name Acct# AcctCloseDate
This Guy 123456 01/01/2004
That Guy 654321 10/15/2004
Since the first date is over 10 months old (way past 90
days) I would leave the row as it is. Since the second
date is only 6 days old I would want to change the whole
row or at least the cell contaning that date to yellow.
Any help with a solution will be greatly appreciated.
Thank you.

Dave Y

Well, you do it with conditional formatting which can also be done via the
worksheet.

I recorded a macro to do the conditional formatting to generate some VBA code.
I assumed your data was in A2:G100 but you can change this and highlight more
or fewer columns and format more or fewer rows.

I assumed your AcctCloseDate was in column C.

=================================
Sub CF()

[a2].Activate
With Range("A2:G100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$C2>(TODAY()-90)"
With Range("A2:G100").FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub
=======================


--ron

 

[Guest]

Hi Ron,

Thanks for your quick reply. I'll try your code or take your suggestion of
using conditional formatting tomorrow when I'm back at work. Thanks again.

Dave Y

Hello,

I need help writing the VBA code to acomplish the
following: I need to find any date in a spreadsheet that
is <= 90 days old; then I want to change the back color of
the row to yellow for those dates. For example if I had
the following data:
Name Acct# AcctCloseDate
This Guy 123456 01/01/2004
That Guy 654321 10/15/2004
Since the first date is over 10 months old (way past 90
days) I would leave the row as it is. Since the second
date is only 6 days old I would want to change the whole
row or at least the cell contaning that date to yellow.
Any help with a solution will be greatly appreciated.
Thank you.

Dave Y

Well, you do it with conditional formatting which can also be done via the
worksheet.

I recorded a macro to do the conditional formatting to generate some VBA code.
I assumed your data was in A2:G100 but you can change this and highlight more
or fewer columns and format more or fewer rows.

I assumed your AcctCloseDate was in column C.

=================================
Sub CF()

[a2].Activate
With Range("A2:G100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$C2>(TODAY()-90)"
With Range("A2:G100").FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 2
End With
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub
=======================


--ron

 

[Ron Rosenfeld]

Hi Ron,

Thanks for your quick reply. I'll try your code or take your suggestion of
using conditional formatting tomorrow when I'm back at work. Thanks again.

You're welcome. Let me know if any problems.


--ron