Need to have Master numbers and corresponding house bill numbers grouped by color

steve400243 · Oct 25th 2017

Hello, I have a worksheet with multiple lines of data, up to 500 +. There is a master number, column C, and there can be multiple house bill numbers in each Master Number. Column D. I need a way to have all the like Master numbers and corresponding house bill numbers, and the corresponding rows and Columns – A thru Y - be shaded a different alternating color. Please see the example, and I’m sorry if I’m not explaining this well enough. Thanks for all help. I could not figure out how to do this. grouping test.xlsx

rikcando · Oct 25th 2017

Simple but it works.

Code

Sub ToggleColor()
Dim StartRow, iRow, EndColumn As Integer
Dim ShadeColor, UseColor As Single
Dim ShadeOn As Boolean


StartRow = 3
EndColumn = 25
'ShadeColor = -0.05  'lighter
 ShadeColor = -0.15  'medium
'ShadeColor = -0.25  'darker


ShadeOn = False




iRow = StartRow
Do
    If Cells(iRow, 3) <> Cells(iRow - 1, 3) Then
        If ShadeOn Then
            ShadeOn = False
            UseColor = 0#
        Else
            ShadeOn = True
            UseColor = ShadeColor
        End If
    End If


    ActiveSheet.Range(Cells(iRow, 1), Cells(iRow, EndColumn)).Select
    With Selection.Interior
        .Pattern = xlNone
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = UseColor
        .PatternTintAndShade = 0
    End With




    iRow = iRow + 1
Loop While Cells(iRow, 3) > ""


End Sub

Display More

steve400243 · Oct 25th 2017

Thanks for your time Rikcando, I appreciate it. It works as needed.

Need to have Master numbers and corresponding house bill numbers grouped by color

Participate now!

Share