This seems easy, but select case and if statements have not worked for me.

Range of cells THESE = D48, I48, N48

Range of cells THOSE = D49, I49, N49

If any cell in THESE change then clear all THOSE.

If any cell in THOSE change then clear all THESE.

Howard

This seems easy, but select case and if statements have not worked
for me.

Range of cells THESE = D48, I48, N48

Range of cells THOSE = D49, I49, N49

If any cell in THESE change then clear all THOSE.

If any cell in THOSE change then clear all THESE.

Howard

Perhaps you're struggling with the effect of not disabling
'EnableEvents' while your code is running?

--
Garry

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

Perhaps you're struggling with the effect of not disabling
'EnableEvents' while your code is running?

--
Garry


I used EnableEvents = False/True but no go with what I tried.

I dumped my failures, I will recreate them and post them.

Howard

I used EnableEvents = False/True but no go with what I tried.

I dumped my failures, I will recreate them and post them.

Howard


Private Sub Worksheet_Change(ByVal Target As Range)

Dim PctCells As Range, AmtCells As Range
Set PctCells = Range("D48,I48,N48")
Set AmtCells = Range("D49,I49,N49")

Application.EnableEvents = False
If Not Application.Intersect(PctCells, Range(Target.Address)) Is Nothing _
Or Application.Intersect(AmtCells, Range(Target.Address)) Is Nothing Then Exit Sub

If Target = PctCells Then AmtCells.ClearContents
If Target = AmtCells Then PctCells.ClearContents
End If

' Select Case Target.Range

' Case Is = PctCells
' AmtCells.ClearContents

' Case Is = AmtCells
' PctCells.ClearContents

' Case Else

' End Select

'End If
Application.EnableEvents = True

End Sub

Howard, you write such hi-maintenance code for this...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ErrExit
Select Case Target.Address
Case "$D$48", "$I$48", "$N$48"
Range("$D$49,$I$49,$N$49").ClearContents

Case "$D$49", "$I$49", "$N$49"
Range("D48,I48,N48").ClearContents
End Select

ErrExit:
Application.EnableEvents = True
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

On Monday, January 26, 2015 at 4:11:56 PM UTC-8, GS wrote:
Howard, you write such hi-maintenance code for this...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ErrExit
Select Case Target.Address
Case "$D$48", "$I$48", "$N$48"
Range("$D$49,$I$49,$N$49").ClearContents

Case "$D$49", "$I$49", "$N$49"
Range("D48,I48,N48").ClearContents
End Select

ErrExit:
Application.EnableEvents = True
End Sub

--
Garry

If my ignorance frustrates you then ignore my posts.

Thanks for the code.

Howard