Using VBA to Select and Highlight Excel Rows
Solution 1
Here is another one based on Mote's .EntireRow.Interior.ColorIndex
This one doesn't restrict you to enter the row numbers but gives the user the flexibility to choose the rows at runtime.
Option Explicit
Sub Sample()
Dim Ret As Range
On Error Resume Next
Set Ret = Application.InputBox("Please select the rows that you would like to color", "Color Rows", Type:=8)
On Error GoTo 0
If Not Ret Is Nothing Then Ret.EntireRow.Interior.ColorIndex = 6
End Sub
FOLLOWUP
Is there a way to write the macro to read the row numbers from a list and highlight the rows?
Yes there is a way. Let's say the list in Cell A1 to A10 then you can use this code
Option Explicit
Sub Sample()
Dim i As Long, sh As Worksheet
On Error GoTo Whoa
Application.ScreenUpdating = False
'~~> Set this to the sheet where the rows need to be colored
Set sh = Sheets("Sheet2")
'~~> Change Sheet1 to the sheet which has the list
With Sheets("Sheet1")
For i = 1 To 10
If Not Len(Trim(.Range("A" & i).Value)) = 0 And _
IsNumeric(.Range("A" & i).Value) Then _
sh.Rows(.Range("A" & i).Value).Interior.ColorIndex = 3 '<~~ Red
Next i
End With
LetsContinue:
Application.ScreenUpdating = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
Solution 2
As an alternative to Motes' answer, you can use conditional formatting.
Eg: select A1:J500, Conditional formatting >> New rule >> Use a formula...
For the formula enter: =OR(ROW()=6, ROW()=10, ROW()=150, ROW()=201)
Solution 3
For basic VBA code, you can always start recording a macro, perform the action, stop recording, look at what code was generated, and then clean that up to do what you want. For example, recording the action of highlighting a row (setting the value of Interior.Color) gives you:
Rows("13:13").Select
Range("C13").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
The selection commands and extraneous Interior properties can be removed giving you:
Rows("13:13").Interior.Color = 65535
Adding in the row multi-select:
Rows("6:6,10:10,150:150,201:201").Interior.Color = 65535
Summary:
- Record macro
- View Excel's version
- Use/Edit what code you need
Solution 4
objWB.Cells(rowNum,201).EntireRow.Interior.ColorIndex = 6
etc
Dan
I just started dabbling with VBA. Fairly basic Excel user, never knew I could make my life easier with macros until recently.
Updated on February 24, 2020Comments
-
Dan over 4 years
How can I tell Excel to highlight rows by their row number. For instance, let's say I wanted row 6, 10, 150, 201 highlighted. Thanks.