Alternate row shading using solid color15

[mrh001]

Hi there, I am very new to the use of Macros and VBA. I have a requirement to highlight alternate rows of a spread sheet (excel2003) with grey shading.

I am using this macro provided by MS support (http://support.microsoft.com/kb/213616):

Sub ShadeEveryOtherRow()
Dim Counter As Integer

'For every row in the current selection...
For Counter = 1 To Selection.Rows.Count
'If the row is an odd number (within the selection)...
If Counter Mod 2 = 1 Then
'Set the pattern to xlGray16.
Selection.Rows(Counter).Interior.Pattern = xlGray16
End If
Next

End Sub


- which does exactly what I want - except I don't like the "xlgray16"shading as the contrast makes it hard to distinguish the contents of the cells.

I much prefer the shading per

.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
-this is what what was recorded when I did a simple "record macro".

My problem is I don't know how to incorporate the above pattern into the shading macro.
Can you help please? Many thanks in advance.

 

[VoG]

Try

Sub ShadeEveryOtherRow()
Dim Counter As Long
'For every row in the current selection...
For Counter = 1 To Selection.Rows.Count
    'If the row is an odd number (within the selection)...
    If Counter Mod 2 = 1 Then
    'Set the pattern to colorindex 15
        Selection.Rows(Counter).Interior.ColorIndex = 15
    End If
Next Counter
End Sub

 

[dave3009]

Hi and welcome

try

Sub ShadeEveryOtherRow()
Dim Counter As Integer

'For every row in the current selection...
For Counter = 1 To Selection.Rows.Count
'If the row is an odd number (within the selection)...
If Counter Mod 2 = 1 Then
'Set the pattern to xlGray16.
Selection.Rows(Counter).Interior.ColorIndex = 15

End If
Next

End Sub

 

[lenze]

If you place this code in a regular module and run it on a Blank Sheet, you can see all the colors on your pallete with the IndexNumber

Sub ShowColorIndex()
   Dim cl As Range
    Dim x As Integer
    x = 0
    Range("$A$3:$A$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .RowHeight = 20
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$C$4:$C$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$E$4:$E$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$G$4:$G$17").Select
    With Selection
     .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ColumnWidth = 17.5
        .Font.Bold = True
    End With
    For Each cl In Selection
    cl.Value = x
    cl.Interior.ColorIndex = x
    x = x + 1
    Next cl
 
    Range("$D$1").Select
    Selection = "COLORINDEX"
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlCenter
    Selection.ColumnWidth = 17.5
End Sub

That said, you don't need a macro to do this. Condifional Formatting will work. Formula Is

=($A1<>"")*(MOD(ROW()-1,2)+1<=1)

lenze