Finding all ranges EXCEPT those with color fill

[Anne Troy]

I have a workbook that contains multiple ranges that are filled with color.

I need to develop the code that will delete ALL OTHER cell contents.

BUT, the worksheet the code will work on will NOT have the fills.

So, what I want to do is find a way to "report" to me all those ranges that are not filled so I can make the code use those ranges to delete the cell contents.

Anybody have any direction for me?

[Zack Barresse]

What do you mean 'all ranges'?? There are a lot of cells in one worksheet.

Also, do you not know the fill color?

[Anne Troy]

Sure, but I only want to select the cells that DON'T have any fill.

[Zack Barresse]

Okay, but that's a lot of cells. Do you not want to trim it down first? All the way to row 65,536??

[Anne Troy]

LOL. Nevermind. I've got it. I recorded a macro selecting all those ranges.

However, if you want to finish up this question, my "inside range" is A1:V1055

So this could be a KB: Select All Cells with Fill Color :)

Or you can just delete the whole dern thread.

[Zack Barresse]

Well, basically you could use this ...


Option Explicit

Sub SelectNONcoloredCells()
Dim cel As Range, rng As Range, tmpC As Range
Set rng = Range("A1:V1055")
For Each cel In rng
If cel.Interior.ColorIndex = -4142 Then
If tmpC Is Nothing Then
Set tmpC = cel
Else
Set tmpC = Union(cel, tmpC)
End If
End If
Next cel
If Not tmpC Is Nothing Then
tmpC.Select
'other code here
End If
End Sub

[Anne Troy]

Cool!!