Selection_Change?

[Sandy]

I have a situation where a lot of my code is executed by "Worksheet_Change".
However there is one part that I would like to add in and I think
"Selection_Change" may be the answer - let me explain:

If a cell is changed in the range ("C9:K9,M9:U9") then I would like the
corresponding value in Offset(6) to be set to "". Something like:-

Row 9 contains integers, row 15 text.

For Each Cell In Range("C9:K9,M9:U9")
If mycell.value is changed then
mycell.offset(6).value = ""
End if
Next

I know the above code is nonsense but a pointer would be useful.
Sandy

 

[Dave Peterson]

It sounds like you want to react to a change in the cell--not just the selection
of one of those cells.

If that's true:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("c9:k9,M9:U9")

Set myRng = Intersect(Target, myRngToCheck)

If myRng Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next 'just ignore any errors
For Each myCell In myRng.Cells
myCell.Offset(6, 0).Value = ""
Next myCell
On Error GoTo 0
Application.EnableEvents = True

End Sub

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Union(Range("C9:K9"), Range("M9:U9"))
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(6, 0).Value = ""
Application.EnableEvents = True
End Sub