PDA

View Full Version : Formula to change cell color based on if it is within two dates



acertco03dis
06-23-2016, 05:59 AM
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
06-23-2016, 07:10 AM
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