VBA to Change front to red if within 30 days

[Tmoske]

How can I use a VBA to change the font color to red for all the dates on a sheet that are within 30 days of today?

Thanks
Tmoske

 

[GTO]

Hmmm... I take it "works great" was referring to the code then?

Okay - presuming there's not a bajillion date cells, I still agree w/Norie that conditional formatting (CV) would be simpler.

Try this in a blank/new workbook:

Select Cell A1

Menubar | Format | Conditional Formatting...

In the drop-down box under Condition1, change to Formula Is.


Type in (WITHOUT the quote marks):

<TODAY()+30

<TODAY()+30"< p>
Note: Excel will insert the equals sign and the quote marks for you.

Now, click the Format... button, make sure the Font tab is active, and change the color.

Click <OK>for both dialogs, and if you type "03/15/09" (or whatever format you use for dates), the font will change to red.

I have to get for a while, so in case you are still having problems with the CV, this would change the dates back to black if the user overwrote with an 'acceptable' date:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngMyUsedRange As Range
Dim rCell As Range
 
    Set rngMyUsedRange = Range("A1:N40")
 
 
    For Each rCell In rngMyUsedRange
        If IsDate(rCell.Value) _
        And rCell.Value < Date + 30 Then
            rCell.Font.Color = &HFF&
        ElseIf IsDate(rCell.Value) _
        And rCell.Value >= Date + 30 Then
            rCell.Font.Color = &H0&
        End If
    Next
End Sub

Hope that helps,

Mark

 

[GTO]

ACK! Ten minute edit rule vs. slow hands Mark...

Sorry about that. The formula would be: "< TODAY()+30"

Again, without the quote marks and you can delete the space between the less-than sign and the "T".

 

[Tmoske]

Mark,
Thanks so much for the help. The longway (VBA) seems to work but not the conditional format.

This is what exactly I'm trying to do.
I have a vehicle list with verious service dates and would like the dates to show up in red when they are within 30 days.

Thanks,
Tmoske

 

[GTO]

Mark,
Thanks so much for the help. The longway (VBA) seems to work but not the conditional format.

This is what exactly I'm trying to do.
I have a vehicle list with verious service dates and would like the dates to show up in red when they are within 30 days.

Thanks,
Tmoske

Happy to help