CODEWITHSUNDEEP
excelvba
rainbowthug
rainbowthug

Reputation: 77

xlToRight fills all the way Right

I wanted to fill in cell color starting from certain column number to the last column, therefore I've used

    Set colour_range = Range(Cells(1, colnum).Offset(0, 1), Cells(1, Columns.Count).End(xlToRight))
colour_range.Interior.Color = RGB(255, 153, 0)

Everything works fine, however the interior.color does not stop at the last not empty cell (like I am using ctrl+right or ctrl+shift+right) but goes all the way with filling till the last column (column XFD).

Any suggestion show to overcome that? Its just a cosmetic issue but it keeps bothering me

Upvotes: 0

Views: 268

Answers (3)

rainbowthug
rainbowthug

Reputation: 77

xlToRight means you want to use the formula to the very end of right

Columns.Count sets you at the very last available column, therefore xlToLeft is proper idea - from last available column all the way to the left (stops at the column chosen at colnum parameter)

Hope it helps you to understand what's going on behind code

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54767

If you have more data to the right of the relevant data (e.g. another table), then if your row is contiguous (has no empty cells), you can use...

Option Explicit

Sub decorateFirstRow()
    
    Const ColNum As Long = 2
    Dim colour_range As Range
    
    Set colour_range = Range(Cells(1, ColNum).Offset(0, 1), _
                             Cells(1, ColNum).Offset(0, 1).End(xlToRight))
    colour_range.Interior.Color = RGB(255, 153, 0)

End Sub

...mimicking CTRL+RIGHT. If it is the last column of the worksheet, then rather use FaneDuru's solution.

Upvotes: 1

FaneDuru
FaneDuru

Reputation: 42236

Please, change:

 Set colour_range = Range(Cells(1, colnum).Offset(0, 1), Cells(1, Columns.Count).End(xlToRight))

with:

 Set colour_range = Range(Cells(1, colnum).Offset(0, 1), Cells(1, Columns.Count).End(xlToLeft))

Cells(1, Columns.Count) means the last cell on the first row.

xlToRight extends the range up to the last cell to the right. If nothing (else) exists, it stays to the last one...

But xlToLeft goes from the last existing column (Columns.Count) to the left until the last existing one.

Upvotes: 1

Related Questions