Adding to Volker's comments/Qs...

Is there always only going to be 1 cell changed at a time?

Are you looking for a mechanism to *undo* previously shaded cells?

Have you tried shading via using Conditional Formatting criteria?

Attempting to process "" as a value for unshading previously shaded
cells is futile at best since there's going to be 10s of 1000s of empty
cells whether you're looking for an empty string returned by a formula
*or* testing if the cell =Empty. As Volker states.., these are 2
different things!<g Better to store the previous value used as
criteria for shading and pull it into play when Target.Value ="" or
=Empty. Simplest way to test for both is to use IsEmpty()...

If IsEmpty(Target) Then lColorNdx = 0 else lColorNdx = 6


Another suggestion/recommendation is to get in the habit of using
object events as triggers for calling some process, but not running the
process within the event...

In the sheet's cde window:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row 4 And Target.Column = 11 Then _
Call Toggle_ChepOut_Shading(Target, Target.Offset(, -9).Value)
End Sub


In a standard module:
Option Explicit

Sub Toggle_ChepOut_Shading(Target As Range, LookupVal)
Dim lColorNdx&, rngFind As Range, sAddr$

If IsEmpty(Target) Then lColorNdx = 0 Else lColorNdx = 6

If IsEmpty(Target) Or IsNumeric(Target.Value) Then
Target.Offset(, -10).Resize(, 14).Interior.ColorIndex = lColorNdx
With Sheets("Outbound").UsedRange
Set rngFind = .Find(What:=LookupVal, _
After:=.Cells(1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rngFind Is Nothing Then
sAddr = rngFind.Address
Do
rngFind.Offset(, -4).Resize(, 14).Interior.ColorIndex =
lColorNdx
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address < sAddr
End If 'Not rngFind Is Nothing
End With 'Wks
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion