PDA

View Full Version : Solved: Coloring rows



Codeblue
07-06-2012, 08:19 AM
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.

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

but I also have a sort feature and I change the color/pattern to solid colors (not alternate rows)

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

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.

Bob Phillips
07-06-2012, 09:31 AM
You have left over CF, maybe delete as you go along

Dim cell As Range
Dim i As Long
For Each cell In Intersect(ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
For i = cell.FormatConditions / Count To 1 Step -1
cell.FormatConditions(i).Delete
Next i
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

Codeblue
07-06-2012, 10:50 AM
I'm getting an error "Argument not optional"
here

For i = cell.FormatConditions / Count To 1 Step -1
cell.FormatConditions(i).Delete

fredlo2010
07-06-2012, 11:13 AM
I think there is a typo there.

For i = cell.FormatConditions.Count To 1 Step -1
cell.FormatConditions(i).Delete

Codeblue
07-06-2012, 01:00 PM
thanks for the input.
This is partially working, it is working for column C only.

CatDaddy
07-06-2012, 01:02 PM
cell.EntireRow.FormatConditions(i).Delete

Bob Phillips
07-06-2012, 02:12 PM
thanks for the input.
This is partially working, it is working for column C only.

Isn't that because your activecell is in column C and you use the column of the activecell?

Codeblue
07-06-2012, 03:20 PM
Yes, you are right. Going to mark this as solved I actually like the effect it is having.

thanks for everyone helping on this!