Combine two Private Sub Worksheet_Change - VBA Macro

[uhguy]

Hi everyone!

Thanks for reading first of all.

I have these two Private Sub Worksheet_Change(ByVal Target As Range) that work fine alone in one sheet, but if I put both together I get errors. I read that they need to be combine into one so there is only one Private Sub Worksheet_Change(ByVal Target As Range) but I have no idea how to even start. Any help is appreciated. Below is both Private subs. Thanks!!

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyPlageD = Range("D6:D50")
Set MyPlageI = Range("I6:I50")

For Each CellD In MyPlageD
        If CellD.Value = "Other" Then
           
              For Each CellI In MyPlageI
                    If CellI.Value <> "" Then
                    CellI.EntireRow.Interior.ColorIndex = xlNone
                    End If
                                        
                    Next
                    
        
        CellD.EntireRow.Interior.ColorIndex = 3
        End If
        
        If CellD.Value <> "Other" Then
        CellD.EntireRow.Interior.ColorIndex = xlNone
        End If

Next

For Each CellI In MyPlageI
                    If CellI.Value <> "" Then
                    CellI.EntireRow.Interior.ColorIndex = xlNone
                    End If

Next

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


    If HasValidation(Range("ValidationRange")) Then
    
      Exit Sub
Else
        Application.Undo
      MsgBox "Your last operation was canceled. " & _
      "It would have deleted data validation rules.", vbCritical
   End If
End Sub

Private Function HasValidation(r) As Boolean

   On Error Resume Next
  x = r.Validation.Type
 If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[uhguy]

SOLVED ----->

Probably not the best way but it worked.

If HasValidation(Range("ValidationRange")) Then
    
                    Set MyPlageD = Range("D6:D50")
                    Set MyPlageI = Range("I6:I50")

                    For Each CellD In MyPlageD
                    If CellD.Value = "Other" Then
                    
                                For Each CellI In MyPlageI
                                If CellI.Value <> "" Then
                                CellI.EntireRow.Interior.ColorIndex = xlNone
                                End If
                                                    
                                Next
                                
                    CellD.EntireRow.Interior.ColorIndex = 3
                    End If
                    
                    If CellD.Value <> "Other" Then
                    CellD.EntireRow.Interior.ColorIndex = xlNone
                    End If

                    Next

                    For Each CellI In MyPlageI
                    If CellI.Value <> "" Then
                    CellI.EntireRow.Interior.ColorIndex = xlNone
                    End If

                    Next
           Else
                Application.Undo
                  MsgBox "Your last operation was canceled. " & _
                  "It would have deleted data validation rules.", vbCritica
End If






'****************
Set MyPlageD = Range("D6:D50")
Set MyPlageI = Range("I6:I50")




For Each CellD In MyPlageD
        If CellD.Value = "Other" Then
        
        
        
                For Each CellI In MyPlageI
                    If CellI.Value <> "" Then
                    CellI.EntireRow.Interior.ColorIndex = xlNone
                    End If
                                        
                    Next
                    
        
        CellD.EntireRow.Interior.ColorIndex = 3
        End If
        
        If CellD.Value <> "Other" Then
        CellD.EntireRow.Interior.ColorIndex = xlNone
        End If

Next

For Each CellI In MyPlageI
                    If CellI.Value <> "" Then
                    CellI.EntireRow.Interior.ColorIndex = xlNone
                    End If

Next


'**************************




'**************************



End Sub



Private Function HasValidation(r) As Boolean


'   Returns True if every cell in Range r uses Data Validation
   On Error Resume Next
  x = r.Validation.Type
 If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function