I have a follow-up question ... so sorry ...
The code doesn't seem to work if one of the cell (computed) within the
row has a value. I have this "estimated due date" column wherein I
compute the date that the task is due based on the priority and the
date that the task was raised.
That is because a referenced cell change doesn't trigger the change event
for monitored cells. It can be circumvented by using the Calculate event to
trigger the code for any change, not efficient but it works
Const WS_RANGE As String = "H1:H10"
'-----------------------------------------------------------------
Private Sub Worksheet_Calculate()
'-----------------------------------------------------------------
Dim cell As Range
For Each cell In Range(WS_RANGE)
Call ColourMe(cell)
Next cell
End Sub
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Call ColourMe(Target)
End If
ws_exit:
Application.EnableEvents = True
End Sub
'-----------------------------------------------------------------
Private Sub ColourMe(ByRef Target As Range)
'-----------------------------------------------------------------
With Target
Select Case .Value
Case "Open":
.Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 3 'red
Case "For Review / Approval":
.EntireRow.Interior.ColorIndex = 6 'yellow
Case "VerifiedParent.Cells(.Row, 1).Resize(, 10)"
.Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 5 'blue
Case "Closed":
.Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 10 'green
Case "Pending":
.Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 38 'rose
Case "Rejected":
.Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 37 'pale blue
End Select
End With
End Sub

Signature
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
> I have a follow-up question ... so sorry ...
>
> The code doesn't seem to work if one of the cell (computed) within the
> row has a value. I have this "estimated due date" column wherein I
> compute the date that the task is due based on the priority and the
> date that the task was raised.
Sara_Chase - 23 Jun 2006 01:18 GMT
Many thanks!

Signature
Sara_Chase