VBA code for if else based on date

[kellie1]

The date is formatted as mm/dd/yyyy in column 'V', all data is found in A2 thru AX1000 and there is a header row.
The data is sorted by date found in column 'V'
Need code to look for previous month found in column 'V' and highlight those rows
if previous month is found stop the code
else find current month and highlight the rows.

Once I have the basic code I can generally change the color selection.


Thanks for any and all help.

[onlyadrafter]

Hello,

do you mean something like this?


Sub COL_V_DATE() For MY_ROWS = 2 To Range("V" & Rows.Count).End(xlUp).Row
If Month(Range("V" & MY_ROWS).Value) = Month(Now) Then
Range("A" & MY_ROWS & ":AX" & MY_ROWS).Interior.Color = vbRed
End If
Next MY_ROWS
End Sub

[SamT]

This is onlyadrafter's excellent code with previous month and stop added.

Sub COL_V_DATE()
'Previous Month
For MY_ROWS = 2 To Range("V" & Rows.Count).End(xlUp).Row
If Month(Range("V" & MY_ROWS)) = Month(Now) - 1 Then '<--- See "-1"
Range("A" & MY_ROWS & ":AX" & MY_ROWS).Interior.Color = vbRed
FoundPRevious = True
End If
Next MY_ROWS

'Current Month
If Not FoundPrevious then
For MY_ROWS = 2 To Range("V" & Rows.Count).End(xlUp).Row
If Month(Range("V" & MY_ROWS)) = Month(Now) Then
Range("A" & MY_ROWS & ":AX" & MY_ROWS).Interior.Color = vbRed
FoundPRevious = True
End If
Next MY_ROWS

End Sub

[kellie1]

Works great, Thanks

[kellie1]

is there a simple modification to also include current year in both of the above [previous month and current month]

[SamT]

If Month(Range("V" & MY_ROWS)) = Month(Now) - 1 And _
If Year(Range("V" & MY_ROWS)) = Year(Now) Then

[kellie1]

the code is giving an error on both lines below....... runtime error 13: type mismatch. Any ideas.

Thanks

If Month(Range("V" & MY_ROWS)) = Month(Now) - 1 Then '<--- See "-1"
If Month(Range("V" & MY_ROWS)) = Month(Now) Then

[SamT]

Any ideas.

The Range is not a Date? See what the Numbers tab of the Cell Format says

[kellie1]

it is coming up custom mm/dd/yyyy

but even when I reformat the column to date mm/dd/yy on the number tab it throws the same message.

[SamT]

This worked for me

Sub testing()
If Month(Range("a6")) = Month(Now) - 1 Then
If Month(Range("a7")) = Month(Now) Then
MsgBox "worked"
End If
End If
End Sub

The Date Format does not matter as long as it is a Date.