Change Whole Column Cell Color

[Nigel Bennett]

How can I use VB to change the color of a whole column

Thanks

 

[Bob Phillips]

Columns("F:F").Interior.ColorIndex = 40

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Nigel Bennett]

Thanks Bob, if I can expand on it a bit, I know the cell I
am in for Example B3 how cn I xtract the column from that
to apply the color to the column, the reason I ask is
because I am doing an essbase retrieve and I have blank
columns, I have some code that finds each blank column (by
looking in a specific row and seeing if a cell is blank)
then it resizes the column, I also want it to color the
column but in this case the cell is only colored. Here is
the code I use

Set LastCol = Cells(5, Columns.Count).End(xlToLeft)
Set rng = Range(Cells(4, 4), LastCol)

For Each oCell In rng
If oCell = "" Then
oCell.ColumnWidth = 1.5
oCell.Interior.ColorIndex = 40
End If
Next oCell

 

[Bob Phillips]

Set LastCol = Cells(5, Columns.Count).End(xlToLeft)
Set rng = Range(Cells(4, 4), LastCol)

For Each oCell In rng
If oCell = "" Then
oCell.ColumnWidth = 1.5
oCell.EntireColumn.Interior.ColorIndex = 40
End If
Next oCell


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bernie Deitrick]

Nigel,

No need to loop:

With Range("4:4").SpecialCells(xlCellTypeBlanks).EntireColumn
.ColumnWidth = 1.5
.Interior.ColorIndex = 40
End With

Note that this will fail if there aren't any blank cells: use On Error
Resume Next before this code if there is any chance of that.

If there is a reason why you check the cells in both rows 4 and 5, then you
could continue to do so:

With Range("4:5").SpecialCells(xlCellTypeBlanks).EntireColumn
.ColumnWidth = 1.5
.Interior.ColorIndex = 40
End With

HTH,
Bernie
MS Excel MVP