Using VBA to Select and Highlight Excel Rows

Question

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.

Answer 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

Answer 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)