Having a mismatch error trying to compare a range of cells (Which is my date column) to todays date. I have Cell A1 =TODAY(), and range B2:B29 are expiry dates. I initially had the message box within the "For each cell" statement, which did work and format the cells properly, but then gave the message box for every expired date it found. Tried bringing the message box outside of the For Statement so it only came up once and BAM! Mismatch error.
Thanks in advance!
Code:
Private Sub Workbook_Open()
Dim TDate As Date
Dim ExDate As Date
TDate = Range("A1")
ExDate = Range("B2:B29")
If TDate < ExDate - 10 Then
MsgBox "Something is about to expire!"
End If
For Each Cell In Range("B2:B20")
If Cell.Value < TDate - 10 And Cell.Value <> "" Then
Cell.Interior.ColorIndex = 19
Cell.Font.Bold = True
If Cell.Value > TDate - 10 And Cell.Value <> "" Then
Cell.Interior.ColorIndex = 2
Cell.Font.Bold = False
End If
End If
Next
End Sub
Thanks in advance!