Alternate Column Shading to be applied only to visible columns
 

Dear Experts:

Below macro applies alternate shading to the used range considering
ONLY visible rows (alternate row shading to visible rows).

Could somebody please re-write the code to perform COLUMN banding
(alternate column shading) instead of row banding (alternate row
shading) . Only visible columns should be considered.

Help is much appreciated. Thank you very much in advance.
Regards, Andreas


Sub ColorRows()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range


CI(0) = xlColorIndexNone
CI(1) = 15 'I actually changed this from her's for light grey
i = 0

Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)

For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
Rng.Rows(c.Row).Interior.ColorIndex = CI(i)
i = 1 - i
Next c
End Sub

Alternate Column Shading to be applied only to visible columns
 

Sub ColorColumns()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range

CI(0) = xlColorIndexNone
CI(1) = 15
i = 0

Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)

For Each c In Rng.Columns
If Not c.Hidden Then
c.Interior.ColorIndex = CI(i)
i = 1 - i
End If
Next c
End Sub
--
Jim Cone
Portland, Oregon USA
( Excel add-in: http://tinyurl.com/ShadeData )




"andreashermle"
wrote in message ...
Dear Experts:

Below macro applies alternate shading to the used range considering
ONLY visible rows (alternate row shading to visible rows).

Could somebody please re-write the code to perform COLUMN banding
(alternate column shading) instead of row banding (alternate row
shading) . Only visible columns should be considered.

Help is much appreciated. Thank you very much in advance.
Regards, Andreas

Sub ColorRows()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range
CI(0) = xlColorIndexNone
CI(1) = 15 'I actually changed this from her's for light grey
i = 0
Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)
For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
Rng.Rows(c.Row).Interior.ColorIndex = CI(i)
i = 1 - i
Next c
End Sub

Alternate Column Shading to be applied only to visible columns
 

Try replacing the two lines you have with these two lines:
For Each c In Rng.Columns("A:IV").SpecialCells(xlCellTypeVisible )
Rng.Columns(c.Column).Interior.ColorIndex = CI(i)

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"andreashermle" wrote:

Dear Experts:

Below macro applies alternate shading to the used range considering
ONLY visible rows (alternate row shading to visible rows).

Could somebody please re-write the code to perform COLUMN banding
(alternate column shading) instead of row banding (alternate row
shading) . Only visible columns should be considered.

Help is much appreciated. Thank you very much in advance.
Regards, Andreas


Sub ColorRows()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range


CI(0) = xlColorIndexNone
CI(1) = 15 'I actually changed this from her's for light grey
i = 0

Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)

For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
Rng.Rows(c.Row).Interior.ColorIndex = CI(i)
i = 1 - i
Next c
End Sub
.

Alternate Column Shading to be applied only to visible columns
 

On May 28, 4:16*pm, "Jim Cone" wrote:
Sub ColorColumns()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range

CI(0) = xlColorIndexNone
CI(1) = 15
i = 0

Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)

For Each c In Rng.Columns
* If Not c.Hidden Then
* * *c.Interior.ColorIndex = CI(i)
* * *i = 1 - i
* End If
Next c
End Sub
--
Jim Cone
Portland, Oregon *USA
( Excel add-in: *http://tinyurl.com/ShadeData)

"andreashermle"
wrote in ...
Dear Experts:

Below macro applies alternate shading to the used range considering
ONLY visible rows (alternate row shading to visible rows).

Could somebody please re-write the code to perform COLUMN banding
(alternate column shading) instead of row banding (alternate row
shading) . Only visible columns should be considered.

Help is much appreciated. Thank you very much in advance.
Regards, Andreas

Sub ColorRows()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range
CI(0) = xlColorIndexNone
CI(1) = 15 'I actually changed this from her's for light grey
i = 0
Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)
For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
Rng.Rows(c.Row).Interior.ColorIndex = CI(i)
i = 1 - i
Next c
End Sub

Hi Jim,
great, thank you very much for your professional help. It works as
desired. There is one thing I would like to ask you.
How would this code have to be changed if I wanted to use RGB values
as cell fill (such as RGB (224, 224, 224))

Regards, Andreas

Alternate Column Shading to be applied only to visible columns
 

On May 28, 4:51*pm, ryguy7272
wrote:
Try replacing the two lines you have with these two lines:
For Each c In Rng.Columns("A:IV").SpecialCells(xlCellTypeVisible )
Rng.Columns(c.Column).Interior.ColorIndex = CI(i)

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"andreashermle" wrote:
Dear Experts:

Below macro applies alternate shading to the used range considering
ONLY visible rows (alternate row shading to visible rows).

Could somebody please re-write the code to perform COLUMN banding
(alternate column shading) instead of row banding (alternate row
shading) . Only visible columns should be considered.

Help is much appreciated. Thank you very much in advance.
Regards, Andreas

Sub ColorRows()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range

CI(0) = xlColorIndexNone
CI(1) = 15 'I actually changed this from her's for light grey
i = 0

Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)

For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
Rng.Rows(c.Row).Interior.ColorIndex = CI(i)
i = 1 - i
Next c
End Sub
.- Hide quoted text -

- Show quoted text -

Dear Ryan,

thank you very much for your kind help. I am afraid to tell you that I
am getting erroneous results, that is too many columns are getting
shaded not only the used range.
I tried Jim's one, that one is working. Anyway, thank you very much
for your professional help.
Regards, Andreas

Alternate Column Shading to be applied only to visible columns
 

'One more time...
'---
Sub ColorColumns_R1()
'Jim Cone - Portland, Oregon USA - May 2010
'Shades every other column in used range (skips hidden columns).
'Uses specified RGB color for the shade color.
Dim bColor As Boolean
Dim Rng As Range
Dim N As Long
Dim C As Long

C = RGB(204, 204, 100) '<<< change to please
Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = xlColorIndexNone

For N = 1 To Rng.Columns.Count
If Rng.Columns(N).Hidden Then
'skip
ElseIf Not bColor Then
Rng.Columns(N).Interior.Color = C
bColor = True
Else
bColor = False
End If
Next 'N
Set Rng = Nothing
End Sub
--
Jim Cone
Portland, Oregon USA
Review of 'Special Sort' Excel add-in at...
http://www.contextures.com/excel-sort-addin.html





"andreashermle"
wrote in message...
Hi Jim,
great, thank you very much for your professional help. It works as
desired. There is one thing I would like to ask you.
How would this code have to be changed if I wanted to use RGB values
as cell fill (such as RGB (224, 224, 224))

Regards, Andreas

Alternate Column Shading to be applied only to visible columns
 

On May 29, 4:10*pm, "Jim Cone" wrote:
'One more time...
'---
Sub ColorColumns_R1()
'Jim Cone - Portland, Oregon USA - May 2010
'Shades every other column in used range *(skips hidden columns).
'Uses specified RGB color for the shade color.
*Dim bColor As Boolean
*Dim Rng * *As Range
*Dim N * * *As Long
*Dim C * * *As Long

*C = RGB(204, 204, 100) *'<<< change to please
*Set Rng = ActiveSheet.UsedRange
*Rng.Interior.ColorIndex = xlColorIndexNone

*For N = 1 To Rng.Columns.Count
* * *If Rng.Columns(N).Hidden Then
* * * *'skip
* * *ElseIf Not bColor Then
* * * * Rng.Columns(N).Interior.Color = C
* * * * bColor = True
* * *Else
* * * * bColor = False
* * *End If
*Next 'N
*Set Rng = Nothing
End Sub
--
Jim Cone
Portland, Oregon *USA
*Review of 'Special Sort' Excel add-in at... *
*http://www.contextures.com/excel-sort-addin.html

"andreashermle"
wrote in message...
Hi Jim,
great, thank you very much for your professional help. It works as
desired. There is one thing I would like to ask you.
How would this code have to be changed if I wanted to use RGB values
as cell fill (such as RGB (224, 224, 224))

Regards, Andreas

Hi Jim,

great job. Thank you very much for your professional help. Regards,
Andreas