Change event

[MAT]

Hi,

I'm trying to write some VBA so that I can autopopulate several cells
at once, unfortunately I'm not very good at it and wondered if anyone
could help?

What I'm trying to do is if a group of cells has the name "CLOP" if one
of that group is changed they all change and then if they're part of
the group "MISC" and one of them changes, then all of the MISC group
changes.

This is what I have. Unfortunately if I change one of the CLOP group it
changes all of the CLOP and all of the MISC and then just keeps on
going!

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value = "Required" Then
Range("[Test1.xls]Sheet1!CLOP").Value = "Required"
ElseIf Target.Value = "In_Progress" Then
Range("[Test1.xls]Sheet1!CLOP").Value = "In_Progress"
ElseIf Target.Value = "Complete" Then
Range("[Test1.xls]Sheet1!CLOP").Value = "Complete"
Else
Range("[Test1.xls]Sheet1!CLOP").Value = ""
Exit Sub
End If
If Target.Value = "Required" Then
Range("[Test1.xls]Sheet1!MISC").Value = "Required"
ElseIf Target.Value = "In_Progress" Then
Range("[Test1.xls]Sheet1!MISC").Value = "In_Progress"
ElseIf Target.Value = "Complete" Then
Range("[Test1.xls]Sheet1!MISC").Value = "Complete"
Else
Range("[Test1.xls]Sheet1!MISC").Value = ""

Exit Sub

End If

End Sub


Any help would be massively appreciated.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("CLOP")) Is Nothing Then
Me.Range("CLOP").Value = Target.Value
ElseIf Not Intersect(Target, Me.Range("MISC")) Is Nothing Then
Me.Range("MISC").Value = Target.Value
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[MAT]

WOW! That was fast

Brilliant

Thank You