Are you sure that

Target.Offset(0, -12)

is valid, that is that the change column has at least 12 columns left?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Peter Rooney" wrote in message
...
Good afternoon, all,

One column in my database ("ColStatus") is validated by a drop down list,
and depending on the value selected, I want the interior colour for all
database cells in the target row to change. In the code shown below, the
msgboxes work fine, so the case logic is OK, but in each case, the colour
doesn't change.
Can any clever person out there suggest why this might be?
The worksheet isn't protected.
Thanks in anticipation

Pete



Private Sub Worksheet_Change(ByVal Target As Range)

Dim DBSheet As Worksheet
Dim ColStatus As Range

Set DBSheet = Sheets("Database")
Set ColStatus = DBSheet.Range("ColStatus")

Set Intersection = Intersect(Target, ColStatus)
If Not Intersection Is Nothing Then
Select Case Target.Formula
Case "Withdrawn"
MsgBox ("Withdrawn")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
3
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
Case "Completed"
MsgBox ("Completed")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
4
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
Case "On Hold"
MsgBox ("On Hold")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
45
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
Case Else
MsgBox ("Other")
Target.Offset(0, -12).Resize(1, 30).Interior.ColorIndex =
38
Target.Offset(0, -12).Resize(1, 30).Font.ColorIndex =
xlAutomatic
End Select
End If
End Sub