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]

Greetings Timoske,

This presumes nothing will be entered outside of A1:N40. Change to suite.

The below goes in the sheet's module, so right-click the sheet's tab, select View Code, and paste in VBIDE.

Hope this helps,

Mark

In the Worksheet Module:

Option Explicit

Private Sub Worksheet_Activate()
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&
        End If
    Next
        
End Sub

 

[Tmoske]

Mark,
Thanks for the reply. I have issue with VBIDE. What is it? and what am I doing with it.

I am new to VBA and I haven't come across VBIDE yet.

Thanks,
Tmoske

 

[Norie]

Why not just use conditional formatting?

 

[GTO]

...so right-click the sheet's tab, select View Code, and paste in VBIDE.

When you right-click the sheet's tab and select View Code, this takes you to VBIDE; that is, the code editing window.

For a simple test, open a new/blank workbook and place this code in the sheet's module:

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&
        End If
    Next
End Sub

Now when you go back to the sheet, try typing in some dates in any cell in the range of A1:N40. The font will change to red presuming the date is less than 30 days from now.

Using the change event (in this second example) might be better, as the font will change as soon as the date is entered (after the cell (ie edit mode) is exited), rather than waiting til the next time the sheet is activated.

A couple of "maybe's" to consider:

• If you already know specific cells that dates will be entered in, you could specify to check just these cells.
• You might want to also change the font back to black if a new date is entered that is over 30 days out.

Mark

 

[Tmoske]

Norie,
How can I get the font of a cell to change to red if it is within 30 days of today?

I just thought a VBA code would be easier

Thanks,
Tmoske

 

[GTO]

Hi Norie - Jeepers, I shoulda thought of that.


To use conditional formatting, change the "Cell Value Is" to "Formula Is" in the drop-down, and for the formula: ="< Today()+30"<TODAY()+30"< p>
Better?

Mark

 

[Tmoske]

Thanks a lot!! Mark
That works great! Just what I needed. Thanks for the explanation.

Thanks so much,
Tmoske

 

[GTO]

Just for my clarity, you settled on the conditional formatting, right? Just asking so as not to leave the font red if you used the code and then a "too close for comfort date" was overwritten with an acceptable date.

Happy to help

Mark

 

[Tmoske]

Mark,
I tried that conditional format formula and can not get it to work.

="< Today()+30"<TODAY()+30"< p>