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.

Reafidy · Accepted Answer · 2012-06-07 00:52:40Z

5

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

edited Jun 7, 2012 at 0:52

Reafidy

8,4796 gold badges53 silver badges86 bronze badges

answered Jun 6, 2012 at 23:25

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

2 Comments

Dan Over a year ago

Hi, just a quick clarification, I should have included it before. The spreadsheet has several thousand records (rows). Is there a way to write the macro to read the row numbers from a list and highlight the rows? This is a daily report I look at. I'd like to automated as much as I can. Then I can filter by color and just see the ones I need.

Siddharth Rout Over a year ago

Yes you can do that. Updating post.

Tim Williams · Accepted Answer · 2012-06-06 22:38:01Z

5

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)

answered Jun 6, 2012 at 22:38

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Comments

Steve Konves · Accepted Answer · 2012-06-06 22:36:25Z

4

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

answered Jun 6, 2012 at 22:36

Steve Konves

2,6763 gold badges26 silver badges44 bronze badges

1 Comment

Siddharth Rout Over a year ago

+ 1 for the detailed answer :)

Motomotes · Accepted Answer · 2017-02-24 06:54:29Z

3

objWB.Cells(rowNum,201).EntireRow.Interior.ColorIndex = 6

etc

edited Feb 24, 2017 at 6:54

answered Jun 6, 2012 at 22:16

Motomotes

4,2471 gold badge28 silver badges25 bronze badges

1 Comment

john k Over a year ago

Why not just Rows(201).Interior.ColorIndex = 6?

Kelsius · Accepted Answer · 2015-03-17 19:56:50Z

0

Update: Didn't realize the date on this, but thought I'd add this in since it was relevant to the chosen answer.

In addition to Siddharth Rout's answer, since I don't have enough rep to comment yet, you can dynamically figure out how many rows there are in your worksheet with these two lines. xlCellTypeConstants could be changed to another XlCellType constant that you need, and the range can always be changed to accommodate to your spreadsheet.

Dim numRows As Integer
numRows = Range("A2", Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeConstants).Cells.Count

answered Mar 17, 2015 at 19:56

Kelsius

4832 gold badges5 silver badges19 bronze badges

Comments

Ray · Accepted Answer · 2016-06-28 19:45:56Z

0

Sorry if it is not as concise or elegant as other answers, but it gets the job done. When I was writing code for my own application I needed to loop through my code. Also, instead of highlighting the entire row, I only needed to highlight a portion of the rows.

Sub Highlight()
Dim ThisWB As Workbook
Dim ThisWS As Worksheet
Dim rows(0 To 3) As Integer
Dim test As String

Set ThisWB = ActiveWorkbook
Set ThisWS = ThisWB.Sheets("Sheet1")

rows(0) = 6
rows(1) = 10
rows(2) = 150
rows(3) = 201

For i = 0 To 3
    test = "A" & rows(i) & ":H" & rows(i)
    ThisWS.Range(test).Interior.ColorIndex = 15
Next i

End Sub

answered Jun 28, 2016 at 19:45

Ray

213 bronze badges

Comments

user6683046 · Accepted Answer · 2016-08-05 14:43:35Z

0

You might be able to achieve the same thing using conditional formatting

put list of values in a column (I use a separate tab and give the list a name)
under conditional formatting - New Rule - "use a formula to determine with cells to format"
read this article http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/
the rule uses vlookup in the formula- =$A2=VLOOKUP($A2,list,1,FALSE)

answered Aug 5, 2016 at 14:43

user6683046

1

Collectives™ on Stack Overflow

Using VBA to Select and Highlight Excel Rows

7 Answers 7

2 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related