👀 Views: 43 💬 Answers: 1 📅 Created: 2025-07-23

vba excel date-formatting

I'm migrating some code and I'm stuck trying to I've looked through the documentation and I'm still confused about I'm relatively new to this, so bear with me... I'm trying to create a VBA macro that formats cells in a specific column based on the date value within each cell. My goal is to highlight cells in red if the date is older than 30 days from today, and green if the date is within the next 7 days. However, I keep working with a 'Type Mismatch' behavior when executing the code, especially with empty cells or cells with non-date values. Here’s the code snippet that I’m currently using: ```vba Sub FormatDateCells() Dim ws As Worksheet Dim cell As Range Dim todayDate As Date todayDate = Date Set ws = ThisWorkbook.Sheets("Sheet1") For Each cell In ws.Range("A1:A100") If IsEmpty(cell.Value) Then cell.Interior.Color = xlNone ' Clear formatting for empty cells ElseIf IsDate(cell.Value) Then If cell.Value < DateAdd("d", -30, todayDate) Then cell.Interior.Color = RGB(255, 0, 0) ' Red for dates older than 30 days ElseIf cell.Value <= DateAdd("d", 7, todayDate) Then cell.Interior.Color = RGB(0, 255, 0) ' Green for dates within the next 7 days Else cell.Interior.Color = xlNone ' Clear formatting for other dates End If Else ' Attempting to clear formatting for non-date values, but this causes a type mismatch cell.Interior.Color = xlNone End If Next cell End Sub ``` I have tried using `IsDate()` to check for valid date entries, but I'm still getting errors when I come across non-date entries. Currently, I'm using Excel 2016, and I suspect that the scenario might be related to how I'm handling the cells after checking for date validity. Any suggestions on how to properly manage this without running into type mismatches? Any help would be greatly appreciated! I'm developing on Windows 10 with Vba. I'd really appreciate any guidance on this. I'm open to any suggestions.