Combine two Worksheet_Change

[Francesca_0208]

I've had a look at the other threads but I'm getting confused and the more I seem to change the more seems to go wrong! I have these two:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = ActiveSheet.Range("d7").Address Then
If Target = "Approval" Then
ActiveSheet.Tab.Color = RGB(255, 192, 218)
ActiveSheet.Range("D7").Interior.Color = RGB(255, 255, 255)
ElseIf Target = "Information" Then
ActiveSheet.Tab.Color = RGB(255, 192, 218)
ActiveSheet.Range("D7").Interior.Color = RGB(255, 255, 255)
ElseIf Target = "C" Then
ActiveSheet.Tab.Color = RGB(255, 192, 218)
ActiveSheet.Range("D7").Interior.Color = RGB(255, 255, 255)
ElseIf Target = "B" Then
ActiveSheet.Tab.Color = RGB(255, 192, 218)
ActiveSheet.Range("D7").Interior.Color = RGB(255, 255, 255)
Else
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
ActiveSheet.Range("D7").Interior.Color = xlNone
End If
End If

Dim cell As Range
Dim Num As Long


For Each cell In Intersect(Target, Range("C11:C38"))
If cell <> "" Then
Application.EnableEvents = False
Num = Application.WorksheetFunction.Match(cell.Text, _
Sheets("Data Fields").Range("Product"), 0)
If Num <> 0 Then cell.Formula = "=INDEX(Product, " & Num & ")"
Application.EnableEvents = True
End If

Next cell
End Sub

&

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim Num As Long


For Each cell In Intersect(Target, Range("C11:C38"))
If cell <> "" Then
Application.EnableEvents = False
Num = Application.WorksheetFunction.Match(cell.Text, _
Sheets("Data Fields").Range("Product"), 0)
If Num <> 0 Then cell.Formula = "=INDEX(Product, " & Num & ")"
Application.EnableEvents = True
End If
Next cell


End Sub

And they seem to work on their own but any way I try to combine the two doesn't work.

Thanks

 

[Shahzadt]

what is the error you are getting

 

[Francesca_0208]

I think I've worked out the problem and I'm getting an error for the second lot of code, it's a Run time error 424, object required. I can only guess it's to do with the Intersect code but I don't really understand it.