VBA Help

  • Thread starter Thread starter SHalper
  • Start date Start date
S

SHalper

I know this is simple so I apologize for posting it. I'm looking for
the VBA code to take a sheet and sort the rows (below row 2) by a
certain column, descending. After sorting, I want Excel to provide
shading in alternating rows. For example:

Row 2 - no shading
Row 3 - Shading
Row 4 - no shading
Row 5 - shading.

Thanks in advance for the help.
 
I know this is simple so I apologize for posting it.  I'm looking for
the VBA code to take a sheet and sort the rows (below row 2) by a
certain column, descending.  After sorting, I want Excel to provide
shading in alternating rows.  For example:

Row 2 - no shading
Row 3 - Shading
Row 4 - no shading
Row 5 - shading.

Thanks in advance for the help.
Click to expand...

hi
you are a tad short on info concerning the sort and shading but....
try this and see if it does what you want.
Sub sortncolorit()
Dim r As Range
Dim lr As Long
Dim rr As Range
Set r = Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight))
'the above line assumes a solid block of data.
Set rr = Range("A3")

r.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rr = Range(rr, "A" & lr)

For Each n In rr
If n.Row Mod 2 = 1 Then
n.EntireRow.Interior.ColorIndex = 15 'gray
End If
Next n
End Sub

you may need to change the sort key. i used column A.
also for the shading, l chose gray. see this site for other excel
colors index numbers.....
http://www.mvps.org/dmcritchie/excel/colors.htm

if this don't do what you want, post back with more info.
regards
FSt1
 
Back
Top