move rows to top with cells in columnA filled RED

alienscript · December 2nd, 2003, 12:15 PM

Hi people

Do you have the VB code that can move the rows to the top with cells background filled in RED color in column (then follow by rows with any cells in column A not being filled in RED color) ?

Alternatively, it will be fine if you have a code that move the rows to the top with any cells in column A that have the font in RED.

Thank for your help in advance.

chrislepingwell · December 2nd, 2003, 12:46 PM

You'll need something like this

Code:

Private Sub ColorSort()
Dim x As Integer, iInsertedRows As Integer

x = Range("A1").End(xlDown).Row

For x = x To 1 Step -1
    If Cells(x, 1).Interior.ColorIndex = 3 Then '3 is standard red color
        Rows(x).Cut
        Rows("1:1").Insert Shift:=xlDown 'paste the cut row back as row 1
        x = x + 1 'we've just moved everything down a row
        iInsertedRows = iInsertedRows + 1
        If iInsertedRows = x Then Exit For 'we don't need to sort the ones we put there
    End If
Next
End Sub

It's basic, but it works. There's plenty of scope for for customisation ;)

HTH Chris

There are two secrets to success in this world:
1. Never tell everything you know

alienscript · December 2nd, 2003, 12:51 PM

Hi Chris,

you are fantastic!
Thank you so much!