Formula to change cell color based on if it is within two dates

[acertco03dis]

Hello everybody.

Currently, I have a range of cells that each have a date within them. On the top of the spreadsheet, I have a beginning and end date that I will change depending on when the spreadsheet is run. Would anyone be able to provide me with a formula or macro that will change the color of the cell with the date to either red or green depending on if that date falls in between the beginning and end date? Any help would be appreciated. Thanks!

[SamT]

Place this code in the Code Page for the Worksheet in question

Dim celStartDate As Range
Dim celEndDate As Range

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Set celStartDate = Range("A1") 'Adjust as needed
Set celEndDate = Range("B1") 'Adjust as needed

If Intersect(Target, celStartDate, celEndDate) Is Nothing Then
    Set celStartDate = Nothing
    Set celEndDate = Nothing
    Exit Sub
End If

SetDateColors

End Sub



Private Sub SetDateColors()
Dim rngDates As Range
Dim FirstCell As Range
Dim LastCell As Range
Dim Cel As Range

'Uncomment after testing
'Application.Screenupdating = False

Set FirstCell = Cells(2, "A") 'Adjust 2 = Row of top of dates, "A" = Dates Column
Set LastCell = Cells(Rows.Count, "A").End(xlUp) 'Adjust "A" to Dates column

For Each Cel In Range(FirstCell, LastCell)
    If Cel >= celStartDate And Cel <= celEndDate Then
        Cel.Interior.ColorIndex = 3
    Else
        Cel.Interior.ColorIndex = xlAutomatic
    End If
Next Cel

Application.ScreenUpdating = True
End Sub