PDA

View Full Version : Alternating row colors while sorting???



infinity
09-16-2007, 07:52 PM
Hello all,

I have got a workbook that I have alternating the row colors from .Interior.ColorIndex = xlnone to .Interior.ColorIndex = 15. This runs when I use a macro that adds a new line at the top of the workbook.

With Range("B11")
If .Interior.ColorIndex = xlNone Then
With Range("B10:J10").Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlSolid
End With
ElseIf .Interior.ColorIndex = 15 Then
With Range("B10:J10").Interior
.ColorIndex = xlNone
.Pattern = xlSolid
.PatternColorIndex = xlNone
End With
End If
End With


It works great but what I want to do is be able to sort Range("B10:J50") in my workbook first by the decending value in column B (which is a date), then by the ascending value in column F (which is a credited dollar amount) then by the ascending value in column E (which is a debited dollar amount).


ActiveSheet.unprotect
Range("B10:J100").Sort Key1:=Range("C10"), Order1:=xlDescending, Header:=xlNo
Dim c As Range
Set c = Range("C11")

While c > Range("K1")
c.Offset(0, -1).Resize(1, 4).Copy
c.Offset(-1, -1).PasteSpecial Paste:=xlValues
c.Offset(0, -1).Resize(1, 6).ClearContents
With c.Offset(0, -1).Resize(1, 5)
.Locked = False
.FormulaHidden = False
End With
With c.Offset(0, -1).Resize(1, 5)
.Locked = True
.FormulaHidden = True
End With
Set c = c.Offset(1, 0)
Wend
c.Offset(-1, -1).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False



What happens when I sort it is the alternating row colors do not follow the new sort grouping greys with other greys and whites with other whites instead of alternating the row color from grey to white and so on. Is there anyone that could take a look at this and let me know what I can do to make this work? Thank you so much!

Bob Phillips
09-17-2007, 12:18 AM
Use conditional fomatting. See http://www.xldynamic.com/source/xld.CF.html#rows

infinity
09-20-2007, 06:39 PM
Hello xld,

As this link said in the beginning, "I probably over use conditional formatting but that's just my style". I too use conditional formatting all the time and in this case I already have 3 conditional formats set for the range that this question was referring to. Any other suggestions?

Bob Phillips
09-21-2007, 01:11 AM
http://www.xldynamic.com/source/xld.CFPlus.Download.html