Anyone know what Color Index is this?

[asalman07]

I ran a macro and I would like to find out the Color Index Number.

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
.PatternTintAndShade = 0
End With



Thank you all!

 

[mumps]

The Color Index Number is 2.

 

[asalman07]

The Color Index Number is 2.

Mumps,

Thanks for your reply but that is giving me a no fill color. The color would be the lightest shade of grey. In Excel 2010, it is 1st box in the 2nd Row under the Color Menu Theme Colors.

Thanks

 

[mumps]

The only way I know of to get that color is to use the RGB values. For example, if you want to fill cell A1 with that shade of grey, you could use this code:

Range("A1").Interior.Color = RGB(242, 242, 242)

I hope this helps.

 

[asalman07]

The only way I know of to get that color is to use the RGB values. For example, if you want to fill cell A1 with that shade of grey, you could use this code:

Range("A1").Interior.Color = RGB(242, 242, 242)

I hope this helps.

Thank you, this gives me the correct color. I should have been more clear in my original post. Sorry for this but I am trying to do alternate color banding with this color you provided. HOwever my whole section is being colored.

Can we do this alternate? Here is the code below.



Sub AlternateColorBanding()
Dim MyRange As Range
Dim MyRow As Range
Set MyRange = Selection
For Each MyRow In MyRange.Rows
If MyRow.Row Mod 2 = 0 Then
MyRange.Interior.Color = RGB(242, 242, 242)
Else
MyRow.Interior.ColorIndex = 2
End If
Next MyRow

End Sub

 

[mumps]

First select the range of rows on which you want to do alternate color banding and then run this macro:

Sub AlternateColorBanding()
    Dim MyRange As Range
    Dim x As Long
    Set MyRange = Selection
    For x = 1 To MyRange.Rows.Count Step 2
        Rows(x).Interior.Color = RGB(242, 242, 242)
    Next x
 End Sub

 

[asalman07]

Mumps,

This does the job great but it always starts with row 1 even if I select my range of rows. Am I doing something wrong?? Thanks again..

First select the range of rows on which you want to do alternate color banding and then run this macro:

Sub AlternateColorBanding()
    Dim MyRange As Range
    Dim x As Long
    Set MyRange = Selection
    For x = 1 To MyRange.Rows.Count Step 2
        Rows(x).Interior.Color = RGB(242, 242, 242)
    Next x
 End Sub

 

[mumps]

How about:

Sub AlternateColorBanding()
    Dim MyRange As Range
    Dim x As Long
    Dim y As Long
    Set MyRange = Selection
    x = Selection.Rows(1).Row
    For y = x To MyRange.Rows.Count Step 2
        Rows(y).Interior.Color = RGB(242, 242, 242)
    Next y
 End Sub

 

[asalman07]

No sir this doesn't work either. Thanks

How about:

Sub AlternateColorBanding()
    Dim MyRange As Range
    Dim x As Long
    Dim y As Long
    Set MyRange = Selection
    x = Selection.Rows(1).Row
    For y = x To MyRange.Rows.Count Step 2
        Rows(y).Interior.Color = RGB(242, 242, 242)
    Next y
 End Sub

 

[djreiswig]

I wasn't sure if your colored rows were supposed to start with the first one in the selection, or odd/even numbered rows, so I'll give you both.

This changes the color of all of the even numbered rows in the selection. To change the odd numbered rows just change the first = to <>

Sub AlternateColorBanding()
    Dim MyRange As Range
    For Each MyRange In Selection.Rows
        If MyRange.Row Mod 2 = 0 Then
            MyRange.Rows.Interior.Color = RGB(242, 242, 242)
        End If
    Next
 End Sub

This one changes the color of the first row of the selection and every other one thereafter.
If you want to start with the second row just change the first = to <>.

Sub AlternateColorBanding()
    Dim MyRange As Range
    For Each MyRange In Selection.Rows
        If MyRange.Row Mod 2 = Selection.Rows(1).Row Mod 2 Then
            MyRange.Rows.Interior.Color = RGB(242, 242, 242)
        End If
    Next
 End Sub