Alternate Column Shading to be applied only to visible columns

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
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
 
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)
 
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
 
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
 
'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
 
'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
 
Back
Top