S
Shelly
I'm using the following code to clear some cells, when other cells are blank.
--------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range
Set rngAllParentCells = Range("A10:A22")
Set rngDepCells = Intersect(Target, rngAllParentCells)
Application.ScreenUpdating = False
If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
'Move 1 cell to the right and clear contents
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
Next rngCell
End If
Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing
End Sub
-------------------------------
This is working just as I want it to. But, now I need to expand it to also
clear another range of cells, too.
For example, if A10 is empty, I need to clear the above identified cells
AND the range C28:E37.
If A11 is empty, clear C41:E50
If A12 is empty, clear C54:E63
If A13 is empty, clear C67:E76
And so on until
If A22 is empty, clear C184:E193
Any ideas? I've tried several things, but no luck.
Thanks!
--------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range
Set rngAllParentCells = Range("A10:A22")
Set rngDepCells = Intersect(Target, rngAllParentCells)
Application.ScreenUpdating = False
If Not rngDepCells Is Nothing Then
For Each rngCell In rngDepCells.Cells
'Move 1 cell to the right and clear contents
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
Next rngCell
End If
Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing
End Sub
-------------------------------
This is working just as I want it to. But, now I need to expand it to also
clear another range of cells, too.
For example, if A10 is empty, I need to clear the above identified cells
AND the range C28:E37.
If A11 is empty, clear C41:E50
If A12 is empty, clear C54:E63
If A13 is empty, clear C67:E76
And so on until
If A22 is empty, clear C184:E193
Any ideas? I've tried several things, but no luck.
Thanks!