Alternate Column Shading to be applied only to visible columns

[andreashermle]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[ryguy7272]

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)

 

[andreashermle]

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" <[email protected]>
wrote in messageDear 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

 

[andreashermle]

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''..












- 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

 

[Jim Cone]

'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" <[email protected]>
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

 

[andreashermle]

'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" <[email protected]>
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