CODEWITHSUNDEEP
excelvba
degers
degers

Reputation: 1

Format selected range macro

I wanted to format a range I select, to simplify things for now, just with the top row in red and the rest of the selection in white.

When my selection starts at A1, everything is fine, however when my selection starts further across the sheet my formatting gets offset from the selection.

It's a long time since I've played with VBA so apologies for the really rookie question!

Here's my code:

Sub formatrange()
Dim myRng As Range
Set myRng = Selection
Dim lc As Long
Dim lr As Long

lr = myRng.Rows.Count
lc = myRng.Columns.Count

myRng.Range(myRng.Cells(1, 1), myRng.Cells(1, lc)).Interior.Color = RGB(153, 0, 0)
myRng.Range(myRng.Cells(2, 1), myRng.Cells(lr, lc)).Interior.Color = RGB(255, 255, 255)

End Sub

Thanks so much all.

Upvotes: 0

Views: 568

Answers (2)

John Alexiou
John Alexiou

Reputation: 29244

Try this code:

Public Sub FormatRange()
    Dim myRng As Range
    Set myRng = Selection


    Dim lc As Long, lr As Long
    lc = myRng.Columns.Count
    lr = myRng.Rows.Count


    myRng.Resize(1, lc).Interior.Color = RGB(153, 0, 0)
    myRng.Offset(1, 0).Resize(lr - 1, lc).Interior.Color = RGB(255, 255, 255)

End Sub

test result

myRng refers to the selected range, and myRng.Resize() changes the number of rows and columns referenced. Also, myRng.Offset() moves the reference down and to the right by specified amount.

So I took the top left reference from myRng and "selected" the top row with .Resize(1,lc)

The I "selected" the remaining table by moving down one row and picking lr-1 rows with .Offset(1,0).Resize(lr-1,lc).

IMHO it is always cleaner to use myRng.Resize(n,m) in place of Range(myRng.Cells(1,1), myRng.Cells(n,m)).

Upvotes: 0

Beek
Beek

Reputation: 406

I think the problem is your lr variable. This gets filled with the amount of rows in your selection. However, if you have let's say 10 rows starting from row 3, then row 10 isn't your last row, row 12 is. So you need to include something like

lr = lr + myRng.Row - 1

To convert your number of rows to the rownumber.

The same goes for your last column of course

Upvotes: 0

Related Questions