COlor Coding specified time frames

[Kim]

I am attemtpting to use validation with date function. Example: I have a
booked date and a specimen date. I want the cell to turn a specific color if
the booked date and specimen date is 0-1 days, another color for 2-3 days,
etc. Is this possible? I can apply conditional formating to a date just one
day greater than my booked date. But that is as far as I can get with the
cell coloring.

 

[Corey ....]

Try this (It is in the WorkSheet Slection Change Event:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
Dim PSP As Worksheet
Set PSP = Worksheets("Sheet1") ' <===== Modify to suit Sheet
For Each cell In PSP.Range("A1:R47") ' <===== Modify to Suit Range
If cell.Value <> "" Then
If (cell.Value - Date) < 1 Then
With cell.Interior
.ColorIndex = 38 ' Modify Colors as required
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) <= 1 Then ' Modify Number of days here
With cell.Interior
.ColorIndex = 36 ' Modify Colors as required
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) > 3 Then ' Modify Number of days here
With cell.Interior
.ColorIndex = 35 ' Modify Colors as required
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) > 1 And (cell.Value - Date) <= 3 Then ' Modify
Number of days here
With cell.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If
End If
Next
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Regards
Ctm...