Need macro to select only columns B through W in active cell row

[wilkisa]

I retired and have been away from Excel for several years. However, my daughter has brought me a problem and I have been able to fix all but the code for this. I need the code to select only cells in the active row in columns B to W, which is 22 cells. Then, color the cells in color index 43. Please note that not all cells are filled so Selection.End(xlToRight)).Select doesn't work because it stops at a blank cell.

I just can't remember how to make this work. Getting too old perhaps?

Any help would be gratefully accepted. It should be easy for the gurus out there.

Thank you so much,
Shirlene

 

[hicksi]

The basic bit of code is the 'Range' statement. How you colour is what you need to supply. I just set them to some colours

Sub aaa()
    Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 22)).Select
    With Selection.Font         'Sets the text font
        .Color = -1003520
        .TintAndShade = 0
    End With
    With Selection.Interior     'Sets the fill colour
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 6299648
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

 

[wilkisa]

Thank you, HIcksi. This fixes the problem. I wasn't using the start as column 2, end as column 23. I was trying to do it in one single selection.

I appreciate your help!

 

[jolivanes]

Sub With_Single_Selection()
    Cells(ActiveCell.Row, 2).Resize(, 22).Interior.ColorIndex = 43
End Sub

 

[wilkisa]

Sub With_Single_Selection()
    Cells(ActiveCell.Row, 2).Resize(, 22).Interior.ColorIndex = 43
End Sub

Thank you, Jolivanes. This is very similar to the change I made using Hicksi's code. I didn't need everything he provided but I just needed the push in the right direction.

 

[hicksi]

Sub With_Single_Selection()
    Cells(ActiveCell.Row, 2).Resize(, 22).Interior.ColorIndex = 43
End Sub

That is so cool. But does it work because Excel VBA is impure?
Things that are being done reflect a range. But 'Cell' isn't natively a range, but can be converted to one by VBA when needed.
But I gotta say the line is pretty cool.

 

[Peter_SSs]

Another way that some people prefer because it uses the column lettering that is familiar to them

Sub Test()
  Intersect(ActiveCell.EntireRow, Columns("B:W")).Interior.ColorIndex = 43
End Sub

 

[hicksi]

Another way that some people prefer because it uses the column lettering that is familiar to them

Sub Test()
  Intersect(ActiveCell.EntireRow, Columns("B:W")).Interior.ColorIndex = 43
End Sub

Is there a book that talks about "cool" excel functions that perform tasks far faster than others?
I have never in my life used INTERSECT. And I know that there are other functions that I have started to use because I saw them used effectively on here. MATCH and OFFSET are definitely amongst those that don't seem to get captured by Record-Macro, and are so useful.

 

[Peter_SSs]

.. "cool" excel functions ..

I don't think functions come with a 'heat rating' as that is very much in the eye of the beholder.

Is there a book ..

Maybe, but I don't use books for this. Virtually everything I know I learnt from this forum, built-in Help and trial and error.