Hi Mick,

Am Fri, 27 Sep 2013 23:11:10 -0700 (PDT) schrieb Living the Dream:

I am wanting to use the following code, which to an extent works, but then fails on the 2nd IF. this code is attached to the Inbound Sheet.

try in code window of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row < 5 Or Target.Column < 11 Then Exit Sub

If IsNumeric(Target.Value) Then
With Target
.Offset(, -10).Resize(, 14).Interior.ColorIndex = 6
lookfor = .Offset(, -9)
End With
Plus_Chep_Out
End If

If IsEmpty(Target) Then
With Target
lookfor = .Offset(, -9)
.Offset(, -10).Resize(, 14).Interior.Color = xlNone
End With
Minus_Chep_Out
End If

End Sub

And in a standard module:

Public lookfor As String

Sub Plus_Chep_Out()
Dim c As Range

Sheets("Outbound").Activate
With ActiveSheet
Set c = .UsedRange.Find(lookfor, .Range("A1"), _
xlValues).Offset(, -4)
End With
If Not c Is Nothing Then _
c.Resize(columnsize:=14).Interior.ColorIndex = 6

End Sub

Sub Minus_Chep_Out()
Dim c As Range

Sheets("Outbound").Activate
With ActiveSheet
Set c = .UsedRange.Find(lookfor, .Range("A1"), _
xlValues).Offset(, -4)
End With
If Not c Is Nothing Then _
c.Resize(columnsize:=14).Interior.Color = xlNone

End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2