How do I change the style of all rows in the selected range?

0
votes

I want to create a macro that will highlight all rows of a selected range of cells. E.g. If I select cells A1 and B3 I want the macro to highlight rows 1 and 3. Currently I have the following macro which is able to highlight a row from a single cell, but I don't know how to expand it to highlight the rows of all selected cells:

Sub Macro1()
    ActiveCell.EntireRow.Style = "Good"
End Sub
1
excelvbaexcel-2013
ActiveCell.Resize(2).EntireRow.Style = "Good" – DeanBDean
@DeanBDean that just highlights 2 rows at and below the lowest of the selected cells. – quant
Whoops, I misread what you were asking. Replace ActiveCell with Selection – DeanBDean
@DeanBDean great thanks :) – quant
@DeanBDean Should make it as Answer, probably with selection type checking first (only apply to Range type: typename(selection) = "Range"). – PatricK

1 Answers

1
votes

I would suggest this as your sub.

Sub Macro1()
     If TypeName(Selection) = "Range" Then
          Selection.EntireRow.Style = "Good"
     End If
End Sub

ActiveCell is only going to return the top left cell within the Selection. See this. Also, thanks to PatrickK for the suggestion about checking the typename, I am embarrassed to say I was unaware of the TypeName function before now.