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

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


--


Gary


"BobR" wrote in message
...
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

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" <GKeramidasATmsn.com wrote in message
...
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


--


Gary


"BobR" wrote in message
...
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

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:D" & lastrow)
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Font.Bold = True
.Font.Italic = True
End With
End Sub


--


Gary


"BobR" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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


--


Gary


"BobR" wrote in message
...
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, Thank you so much, No I don't have any blanks there and this worked
exactly as I needed.
I appreciate the assistance.
BOB

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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:D" & lastrow)
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 36
.Interior.Pattern = xlSolid
.Font.Bold = True
.Font.Italic = True
End With
End Sub


--


Gary


"BobR" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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


--


Gary


"BobR" wrote in message
...
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