Excel Question

  steviegee 10:22 18 Feb 2009

I have a column of numbers with various numbers in. Some the of cells are coloured in. Is there a way I can sort them so that all the coloured cells are first or a formula that will delete all the non coloured cells? Thanks.

  Picklefactory 11:30 18 Feb 2009

What version of Excel are you using?

  steviegee 11:34 18 Feb 2009


  Picklefactory 11:42 18 Feb 2009

Pity you don't have 2007, it is a standard feature with that.
However, does this help?
click here

  VoG II 11:43 18 Feb 2009

Assuming that it is column A and that bit is fill colour that you're interested in (not font colour) try

Sub DelCells()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
With Range("A" & i)
If .Interior.ColorIndex = xlNone Then .Delete shift:=xlShiftUp
End With
Next i
End Sub

  Picklefactory 11:55 18 Feb 2009

Just noticed that the original code on the link sorts with coloured cells at the bottom, not top as you requested. If you want colours at the top it would be as below. But VoG™'s is simpler.

Sub SortByColor()
On Error GoTo SortByColor_Err

Dim sRangeAddress As String
Dim sStartCell As String
Dim sEndCell As String
Dim rngSort As Range
Dim rng As Range

Application.ScreenUpdating = False

sStartCell = InputBox("Enter the cell address of the " & _
"top cell in the range to be sorted by color" & _
Chr(13) & "i.e. 'A1'", "Enter Cell Address")

If sStartCell > "" Then
sEndCell = Range(sStartCell).End(xlDown).Address
Set rngSort = Range(sStartCell, sEndCell)
For Each rng In rngSort
rng.Value = rng.Offset(0, 1).Interior.ColorIndex
Range(sStartCell).Sort Key1:=Range(sStartCell), _
Order1:=xlDescending, Header:=xlNo, _
End If

Application.ScreenUpdating = True
Set rngSort = Nothing
Exit Sub

MsgBox Err.Number & ": " & Err.Description, _
vbOKOnly, "SortByColor"
Resume SortByColor_Exit
End Sub

  steviegee 11:57 18 Feb 2009

Thankyou both for your help. VoGs worked a treat.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?