Great stuff Paul.
Thanks very much :)
--
Jock


" wrote:

Hi
This assumes there is a date in column J when you click the cell in
column M. You can format the date in J as you like.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim myRow As Range
Dim TempDays As Variant, CellColour as Variant

If Not Intersect(Target, ActiveSheet.Columns("M")) Is Nothing Then
TempDays = Date - DateValue(Target.Offset(0,-3).Text) 'days
between today and date in column J
'choose cell colour based on date in J
If TempDays 42 Then
CellColour = 3 'red
elseIf TempDays21 Then
CellColour = 46 'orange
else
CellColour = 6 'yellow
End if
'colour the cells
Set myRow = Target.offset(0,-12).Resize(, 26) 'go back to "A"
With myRow
If .Interior.ColorIndex = xlNone Then
.Interior.ColorIndex = CellColour
Else
.Interior.ColorIndex = xlnone
End If
End With
End If
'Cancel = True
End Sub

regards
Paul