Excel Macro to Highlight All Columns in Selected Rows

Question

I have a macro in my workbook that is tied to a hotkey which highlights all columns in the currently selected row. However, it only works if one row is selected. I cant think of a way to adjust it to highlight all the rows if multiple are selected. Here is the code that I am currently using.

Sub highlight_done()
'
' highlight_done Macro
'
' Keyboard Shortcut: Ctrl+q
'

Dim r As Long
    r = ActiveCell.Row
    
    Range("A" & r & ":Y" & r).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Color = vbWhite
        .TintAndShade = 0
    End With

End Sub

Any help would be appreciated.

BigBen · Accepted Answer · 2020-06-26 13:24:40Z

2

Perhaps like the following, using Intersect and Selection.EntireRow to get the range to be colored:

Sub highlight_done()
'
' highlight_done Macro
'
' Keyboard Shortcut: Ctrl+q
'

    If Not TypeOf Selection Is Range Then Exit Sub
    
    Dim rng As Range
    Set rng = Intersect(Selection.EntireRow, Range("A:Y"))
    
    With rng.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With rng.Font
        .Color = vbWhite
        .TintAndShade = 0
    End With

End Sub

edited Jun 26, 2020 at 13:24

answered Jun 26, 2020 at 13:18

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Macro to Highlight All Columns in Selected Rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related