This post is somewhat a continuation of another solved post I hope that's legal.
I used this code to alternate colors of a ss upon opening it and it works well.
[vba]
Range("A2:G" & Range("G" & Rows.Count).End(xlUp).Row).Select ' color alternate rows
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD( ROW( ), 2) =0"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
End With
.FormatConditions(1).StopIfTrue = True
End With
Range("A1").Select
[/vba]
but I also have a sort feature and I change the color/pattern to solid colors (not alternate rows)
[vba]
Dim cell As Range
For Each cell In Intersect(ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
Select Case cell.Value
Case Is = "Steve Brock"
cell.EntireRow.Interior.ColorIndex = 15
Case Is = "Richard Capp"
cell.EntireRow.Interior.ColorIndex = 37
Case Is = "Brian Garlie"
cell.EntireRow.Interior.ColorIndex = 38
Case Is = "Johhny Han"
cell.EntireRow.Interior.ColorIndex = 15
Case Is = "Bruce McCormick"
cell.EntireRow.Interior.ColorIndex = 17
Case Is = "Kevin McDonald"
cell.EntireRow.Interior.ColorIndex = 33
Case Is = "Bruce Clark"
cell.EntireRow.Interior.ColorIndex = 31
Case Is = "Micki Marion"
cell.EntireRow.Interior.ColorIndex = 22
Case Else
cell.EntireRow.Interior.ColorIndex = 35
End Select
Next cell
[/vba]
but when I execute the sort routine it has a holdover so to speak from the previous coloring routine and it keeps the every other colored row along with the new colors. In other words it sort of hybrids the two routines. I know the sort/color routine works since if I comment out the alternate colr row routine it does exactly what I want with the solid color and so on. I wonder if I should put the rows back to white within the sort/color routine just before changing them to solid.
