If it clears the Target cell address on each sheet, then you have a problem
beyond this, because your code isn't supposed to execute unless that cell is
already clear. Anyway, try this modification

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
Dim Target1 as Range
On Error Resume Next
If Intersect(Target, Range("a3:a" & ActiveSheet _
.UsedRange.Rows.Count - 2)) Is Nothing Then Exit Sub
For Each sh In ActiveWindow.SelectedSheets
set Target1 = sh.Range(Target.Address)
If Target1 = "" Then
Target1.EntireRow.SpecialCells(xlCellTypeConstants ).ClearContents
End If

sh.Range("SortRange").Sort key1:=sh.Range("A3"), header:=xlNo
'sh.Range("A2").Select ' can't select on a sheet that isn't active

Next
Sheets(2).Select
End Sub

--
Regards,
Tom Ogilvy


"David" wrote in message
...
Hi all,

I've been hacking around for a few hours now :(

I'm trying to clear constants from target rows on 2 sheets when I select
both sheets, click on a name and hit Delete

The following works:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("a3:a" & ActiveSheet.UsedRange.Rows.Count -
2)) Is Nothing Then Exit Sub
If ActiveWindow.SelectedSheets.Count 1 Then
If Target = "" Then
Target.EntireRow.SpecialCells(xlCellTypeConstants) .ClearContents
Sheets(4).Range(Target.Address).EntireRow.SpecialC ells
(xlCellTypeConstants).ClearContents
End If
Range("SortRange").Sort key1:=Range("A3"), header:=xlNo
Sheets(4).Range("SortRange").Sort key1:=Sheets(4).Range("A3"), header:
=xlNo
Range("A2").Select
Sheets(2).Select
End If
End Sub

I tried to streamline things with this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
On Error Resume Next
If Intersect(Target, Range("a3:a" & ActiveSheet.UsedRange.Rows.Count -
2)) Is Nothing Then Exit Sub
For Each sh In ActiveWindow.SelectedSheets
If Target = "" Then
sh.Range(Target.Address).EntireRow.SpecialCells
(xlCellTypeConstants).ClearContents
End If
sh.Range("SortRange").Sort key1:=sh.Range("A3"), header:=xlNo
sh.Range("A2").Select
Next
Sheets(2).Select
End Sub

But this line doesn't clear the entire row on the second sheet, only the
name I clicked on:

sh.Range(Target.Address).EntireRow.SpecialCells
(xlCellTypeConstants).ClearContents

Can anyone help?

--
David