CODEWITHSUNDEEP
vbasortingexcelexcel-2007
Kurt Wagner
Kurt Wagner

Reputation: 3315

Record Macro Not Recording Proper Sort Code (Different Results than by Manual Method Recorded From)

So I have a 'Flag' column that I conditional formatted with =IF(INDIRECT("RC",0)="",TRUE,FALSE), which changes font color so that the font color is a different color (blue) if the cell is blank since when I manually sort by value, the blank cells end up on bottom. When I sort manually by font color, everything works just fine.

Manual Sort: enter image description here
Manual Sort Results:
enter image description here

The idea is to make this a macro, so I recorded me doing the manual sort and got the following:

Range("A1:F9").Select
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add(Range( _
    "E2:E9"), xlSortOnFontColor, xlDescending, , xlSortNormal).SortOnValue.Color = _
    RGB(0, 0, 0)
ActiveWorkbook.Worksheets("Sheet1").SORT.SortFields.Add Key:= _
    Range("D2:D9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").SORT
    .SetRange Range("A1:F9")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

However, when I run the recorded code I get the exact opposite of what I want, with the cells with blank flags on bottom:
enter image description here

I've tried changing xlSortOnFontColor's xlDescending to xlAscending, but it still has the same non-desired result.

Question: Given that I recorded a macro for a manual process that works, why is the code recorded not working and what should the code be?

Upvotes: 1

Views: 734

Answers (1)

Andy G
Andy G

Reputation: 19367

You haven't selected the font-color to sort by:

.SortOnValue.Color = _
RGB(0, 0, 0)

This also shows in your Sort screenshot. Record the macro again and select the font-color specifically to put it on top. Recording again is easier as otherwise you'll need to find out the RGB-code for the colour you have chosen.

The recording process is still far from perfect, but it remains a very useful tool.

Upvotes: 3

Related Questions