Isolating Excel cells with particular shading

[EVANSY]

I have a column in Excel containing approximately 12,000 records.

However, I only wish to isolate those cells which are shaded in a particular colour. The rest can be deleted.

As I understand it, this cannot be done in Excel but can be done via VB code. Any ideas ?

Thanks all

 

[SkipVought]

Hi,

I just wrote a FAQ on this...

faq68-6657

Skip,

_[red][/red]

 

[combo]

You can add custom function that gets cell's given propertiy, add helper column with this function, filter by it, copy result to another place. Sample function:

Function CellShading(InputCell As Range, Index As Integer) As Variant
Select Case Index
Case 1 ' ColorIndex
    CellShading = InputCell.Interior.ColorIndex
Case 2 ' PatternColorIndex
    CellShading = InputCell.Interior.PatternColorIndex
Case 3 ' Pattern
    CellShading = InputCell.Interior.Pattern
Case 4 ' Bottom border line style
    CellShading = InputCell.Borders(xlEdgeBottom).LineStyle
Case Else
    CellShading = "Function under construction"
End Select
End Function

combo

 

[Loomah]

hi
this may not be the most elegant solution but it should work (built in xl97)

Sub sonic()
Dim c As Range
Dim r As Range
For Each c In Selection
    If c.Interior.ColorIndex <> 6 Then
        If Not r Is Nothing Then
            Set r = Union(r, c)
        Else
            Set r = c
        End If
    End If
Next
r.Select ' only used as flag - replace with deletion code
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​