Selecting portions of a column instead of the whole colum

[BobR]

Hello, XL 2003 and 2007.
Have the code listed below that selects an entire column and then colors and
formats it. My problem is that I don't need the entire column but only D4
down. D4 contains the beginning statement for my pivot table. The length of
the column will be variable and I'm not sure how to put the code in to take
it from D4 down to the last complete cell.

Can someone help with the code, and the coloring is supposed to be for the
range and not the entire column
Thanks
BOB R




ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
With Selection
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Selection.Font.Bold = True
Selection.Font.Italic = True

 

[Gary Keramidas]

you don't really specify where your data starts. this should illustrate what's
actually happening, although there's a shorter way.
i assumed the active cell was in row 1. if you could be more specific instead of
using activecell, it would be better.

Sub test()
Dim lastrow As Long
lastrow = Cells(Rows.Count, ActiveCell.Offset(0, 1).Column).End(xlUp).Row
With Range(ActiveCell.Offset(0, 1).Address, Cells(lastrow,
ActiveCell.Offset(0, 1).Column).Address)
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Font.Bold = True
.Font.Italic = True
End With
End Sub

 

[BobR]

Thank you Gary, Sorry I'm sorta new at this and used a record macro to get
where I am.

The active cell will need to be set and it will be set at D4. So if I set
the active cell and then then it will go down to the xlUP and select that
range??? At this point I want it to placed the background color in these
cells.
Then it will color it and make the font bold italic.

Thanks again.
Bob

 

[Gary Keramidas]

try this and see if it works, i don't know if you have any blank cells in the
range or not

Sub test()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
With Range("D4" & lastrow)
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Font.Bold = True
.Font.Italic = True
End With
End Sub

 

[BobR]

Gary, Thank you so much, No I don't have any blanks there and this worked
exactly as I needed.
I appreciate the assistance.
BOB