VBA comparison operator not working

[JessWhyCuh]

Hey all,

A little background on my vba project: When the date in a cell is a past date, the cell style changes to "Bad".
I am trying to code this logic: If you change a past date to today's or a future date, then change a cell in another column, but same row, to the style "Neutral". For some reason this line of code only works with the <, <=, or = operator. It does not work with > or >= operators which is what I need. Why? Or if you know a better way to code this please share because I don't understand why it doesn't work this way. Thank you! Down below is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Style = "Bad" And Target.Value>= Date Then Cells(Target.Row, "W").Style = "Neutral"
End Sub

 

[RoryA]

That should work. I suggest you step through the code and check that the value and style are what you think they are.

 

[Leith Ross]

Hello JessWhyCuh,

The problem is with the event firing sequence. The Change event does not fire until after the change to cell has been made. To make this work you have to save the value before the change happens. This can be done using a Worksheet Selection-Change event and a worksheet level variable to save the cell value before it changes.

Be sure to erase any previous Change?Selection_Change event code before you copy and paste this code into your worksheet module.

Dim PrevDate As Date


Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
        If IsDate(Target) Then
            If Target >= Date Then Target.Style = "Normal" Else Target.Style = "Bad"
            Target.NumberFormat = "m/dd/yyyy"
        End If
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If IsDate(Target) = True Then PrevDate = Target.Value
End Sub

How to Paste a Macro into a Worksheet module

• Copy the macro with Ctrl+C.
• Right Click the Sheet Tab at the bottom of the worksheet.
• Click View Code
• Paste the macro into the module with Ctrl+V.
• Save the macro using Ctrl+S

 

[JessWhyCuh]

I added a breakpoint and stepped through it. It's giving me the correct values.

I changed the statement to an Or instead of an And which fixed the problem. However, now it will change the cell style to neutral if I enter a past date which is not on the agenda :/ Could you explain why that is? If not, that's alright because I can't seem to figure it out.

So future dates, i.e. tomorrow, are categorized with the style "Good". When I enter tomorrow's date, the Date value is today's and the Target.Style value is "Good", could this be why the And statement does not work?

P.S. I am pretty new to VBA

 

[JessWhyCuh]

Leith Ross,

Thanks so much! However, there's a typo -- in the first If statement IsDate should be IsDate(PrevDate) not (Target)

 

[Leith Ross]

Hello Jess,

Nice catch on my macro. I had to leave suddenly and wanted to get the code posted for you before I left. Thanks for posting the correction.

 

[JessWhyCuh]

Actually there's a problem I only want the cell to change to the style "Neutral" if it is a past date, i.e. yesterday. The code will change the style for past, present, and future. I'm not sure how to code it for if the cell contained a date in the past.

 

[JessWhyCuh]

Nevermind, I got it