Apply Excel VBA macro to all highlighted cells

Question

Thank you in advance, I am very new to excel VBA. This question may be very elementary, but I haven't found the answer in an extensive search. I originally recorded this macro and tweaked it with stuff I've found online. This macro works if you apply to one cell at a time (or if you drag across multiple rows, will work on the row of the top-left-most cell). Is there a way I can further tweak it to get my macro to apply the changes to the rows of all selected cells so that the user can make changes to rows in bulk?

Range("A" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
Range("A" & ActiveCell.Row).Select
ActiveCell.FormulaR1C1 = "5"
Range("B" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
With Selection.Font
    .Name = "Calibri"
    .FontStyle = "Regular"
    .Size = 11
    .Strikethrough = True
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With
Range("B" & ActiveCell.Row).Select

End Sub

andy holaday · Accepted Answer · 2014-01-22 00:25:19Z

1

Maybe this is what you're after?

'Instead of this:
'Range("A" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
'Do this:
With Application.Intersect(Selection.EntireRow, Range("A:I")).Interior
'The range at hand is now all the cells in the rows of the selection, 
'  but limited to columns A:I.
'Notice we haven't actually modified the selection
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
'Range("A" & ActiveCell.Row).FormulaR1C1 = "5"
Application.Intersect(Selection.EntireRow, Range("A:A")).FormulaR1C1 = "5"
'Range("B" & ActiveCell.Row & ":I" & ActiveCell.Row).Select
With Application.Intersect(Selection.EntireRow, Range("B:I")).Font
    .Name = "Calibri"
    .FontStyle = "Regular"
    .Size = 11
    .Strikethrough = True
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With
Range("B" & ActiveCell.Row).Select

Note: It is not necessary to .SELECT a range and then do something. You can usually just apply something to the range. What you're starting with is typical for macro recorder code, just know there is a cleaner way.

answered Jan 22, 2014 at 0:25

andy holaday

2,3021 gold badge17 silver badges25 bronze badges

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

2 Comments

user3221324 Over a year ago

Yes, this does exactly what I want it to. Thank you for the help! Is there a way I can get the macro to leave the user with the same cells selected as before they ran the macro? Like if they Ctrl+click multiple cells and then apply this formatting macro to the cells, their same cells are selected afterward? Thanks!

andy holaday Over a year ago

Sure -- just comment out the very last line so it looks like 'Range("B" & ActiveCell.Row).Select.

Collectives™ on Stack Overflow

Apply Excel VBA macro to all highlighted cells

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related