Shelly,
Based on your description and current code, I think this code will
accomplish what you're trying to do. If any cell between A6 and A51 is
cleared, the entire row is cleared, and F:row number in "Worksheet2" is also
cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 5 And Target.Row < 52 Then
If Target.Text = "" Then
Target.EntireRow.ClearContents
ThisWorkbook.Worksheets("Worksheet2").Range("F" &
Target.Row).ClearContents
End If
End If
End Sub
"Shelly" wrote:
> Hello -
>
> Need a little help...
>
> When the user deletes data from a cell in one worksheet, I need to clear a
> range in another worksheet that corresponds to the row where the cell was
> deleted.
>
> Below is the code I'm using now in worksheet "Worksheet1". When A:"row
> number" is cleared by the user in worksheet "Worksheet1", it will clear the
> rest of that line. I need to add to this so when the cell is cleared, it
> also clears the range F:"row number" from the "Worksheet2" worksheet.
>
> ----------------------------------------------
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rngAllParentCells As Range
> Dim rngDepCells As Range
> Dim rngCell As Range
>
> Set rngAllParentCells = Range("A6:A51")
> Set rngDepCells = Intersect(Target, rngAllParentCells)
>
> Application.ScreenUpdating = False
>
> If Not rngDepCells Is Nothing Then
> For Each rngCell In rngDepCells.Cells
> rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=2).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=3).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=4).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=5).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=6).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=7).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=8).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=9).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=10).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=11).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=12).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=13).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=14).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=15).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=16).ClearContents
> rngCell.Offset(RowOffset:=0, ColumnOffset:=17).ClearContents
> Next rngCell
> End If
> Set rngAllParentCells = Nothing
> Set rngDepCells = Nothing
> Set rngCell = Nothing
>
> End Sub
>
> -------------------------------
>
> Since it's using a variable to identify the row and column, I'm not sure how
> to identify the corresponding row and column in the other workbook.
>
> Any help appreciated - THANKS!
>
|
Similar Threads
