Macro - Loop selected-cell formatting for multiple rows

[muckem333]

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?

Private Sub Button_DeleteRow_Click()
        
    Selection.EntireRow.Interior.ColorIndex = 3
    
    msg1 = MsgBox("Delete this row?", vbYesNo)
    If msg1 = vbYes Then
    
    Selection.EntireRow.Delete
    
    Else

    ScreenUpdating = False

    Selection.EntireRow.Interior.ColorIndex = xlNone
       
        With ActiveCell
        Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
        Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
        Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
        Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
        Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
        End With

    End If

End Sub

 

[RSXchin]

let me know if this works

Private Sub Button_DeleteRow_Click(ByVal Target As Range)
    Selection.EntireRow.Interior.ColorIndex = 3
    
    msg1 = MsgBox("Delete this row?", vbYesNo)
    If msg1 = vbYes Then
    
    Selection.EntireRow.Delete
    
    Else

    ScreenUpdating = False

    Selection.EntireRow.Interior.ColorIndex = xlNone
    For Each R In Selection
        Range(Cells(Target.Row, "AA"), Cells(Target.Row, "AN")).Interior.ColorIndex = 15
        Range(Cells(Target.Row, "c"), Cells(Target.Row, "d")).Interior.ColorIndex = 15
        Range(Cells(Target.Row, "a"), Cells(Target.Row, "a")).Interior.ColorIndex = 15
        Range(Cells(Target.Row, "j"), Cells(Target.Row, "j")).Interior.ColorIndex = 15
        Range(Cells(Target.Row, "n"), Cells(Target.Row, "n")).Interior.ColorIndex = 15
    Next
    End If

End Sub

 

[muckem333]

rsxchin,

Thanks for the quick response. I am already using a separate (ByVal Target As Range) change event within this particular worksheet. I think that is causing a compile error : "procedure declaration does not match description of event or procedure having the same name". Arent you only allowed to have one per worksheet?

Is there another way around it?

Here is part of the other event i have on the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("Segment")) Is Nothing Then
    
    ActiveSheet.Unprotect Password:=PSWD

    ScreenUpdating = False
    
    If Range("Segment").Value = "" Then
    Range("Hidesurg1").EntireColumn.Hidden = False
    Range("HideIP1").EntireColumn.Hidden = False

[/QUOTE]