Changing Multiple ranges to Dynamic ones

[Grade4.2]

Hello Everyone,

I'll be using excel 2007 to run this spreadsheet.
I have uploaded my workbook for reference.
In the data dump tab, I copy and paste the data you see from another spreadsheet.
When I run the macro to sort the green colors in descending order, I get an error because the ranges in macro "Sortv2" aren't dynamic. The beginning of my recorded macro and the ranges i'm referring to are as follows:

Sub Sortv2()
'
' Sortv2 Macro
'


'
Sheets("DataDump").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("DataDump").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DataDump").Sort.SortFields.Add(Range("D2:89"), _
xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(196, _
215, 155)
ActiveWorkbook.Worksheets("DataDump").Sort.SortFields.Add(Range("E2:E89"), _
xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(196, _

[SamT]

I don't know about Excel V2010, but in XP, Conditional Formatting Cell Colors don't actually "belong" to the Cell. The Conditional Colors, are "Overlaid" over the spreadsheet, not put in the Cell.
IOW, I can't access Cell Colors with conditional Formatting via VBA.

While XP cannot handle all the Conditional Formats 2010 can, what I can see is that the CFs are mostly >0, so a simple Sort Descending should work for you

[Paul_Hossler]

1. Must have taken a long time to make that macro

2. I like to do 'dynamic' sorts like this




Option Explicit

Sub Sortv3()
Dim r As Range, r1 As Range
Dim i As Long

With Worksheets("DataDump")

Set r = .Cells(1, 1).CurrentRegion
Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)

MsgBox r.Address
MsgBox r1.Address

With .Sort
.SortFields.Clear

For i = 4 To 63 ' D to BK
.SortFields.Add(r1.Columns(i), xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(196, 215, 155)
Next i

.SetRange r
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

.Range("A2").Select
End With

Sheets("TODAY").Select
Range("B5:C5").Select
End Sub

[Grade4.2]

That was perfect! I really wish I knew more about what you did. That macro took me ages! haha. In my current state, I can only record VBA and hope for the best. THANK YOU THANK YOU THANK YOU!

[Grade4.2]

Oh what i had to do in order to create that macro was, while recording, shift+alt right arrow, down arrow.... press sort on the data tab and sort the cell by color in that pop up box thing... for like heaps of columns. So dramatic when looking at the VBA below haha.

[Paul_Hossler]

No problem

Just trace your way through - you'll get it

Probably shorter ways, but I go for straight forward approach