Hi Guys

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.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row < 5 Then Exit Sub

On Error GoTo ErrHandler

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

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

Call Module8.Plus_Chep_Out

If Target.Value = "" Then
With Target
.Offset(, -10).Resize(, 14).Interior.ColorIndex = 2
.Offset(, -9).Select
End With

Call Module8.Minus_Chep_Out

End If
End If
End If

ErrHandler:

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

If I enter a number in column 11, it fires the "Plus_Chep" call, but! When I delete the number from the same cell, I fully expect it to call "Minus_Chep"and remove the cell colouring from the Outbound sheet, but it does not.

Both Call codes are identical with the exception that one colours and the other removes it.

The frustrating part is that it partially fires the Minus_Chep call by activating the sheet in question and going to the matching cell in the find: criteria but does not remove the colouring.

I was considering using a Case Select but wasn't quite sure which would be the best approach for it.

As always

Heaps of thanks in advance, and my glass is still half full.

Cheers
Mick.

Sub Plus_Chep_Out()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

lookfor = Selection.Value
Sheets("Outbound").Activate
Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select

With Selection
.Offset(, -4).Resize(, 14).Interior.ColorIndex = 6
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Sub Minus_Chep_Out()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

lookfor = Selection.Value
Sheets("Outbound").Activate
Cells.Find(What:=lookfor, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select

With Selection
.Offset(, -4).Resize(, 14).Interior.ColorIndex = 2
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub