finding any coloured cell

[prabhafriend]

Today we had to solve a very peculiar but seemingly very very easy scenario but its not as we thought. Actually the task we assigned is to check a workbook for filled cells i.e filled by any color. If there is a filled one then we have to report 'no' in a statement. Yeah initially i also thought the same idea. Going cell by cell and evaluating its colorindex. To say you the truth i hates this idea. Actually i want to achieve this in a smartest way possible. First i considered about the 'find and replace' functionality. I started to record a macro entered no string in the text to find and selected the 'no fill' in the formats. I eagerly expected to find an expression like this 'where cell.fill color = none' and dreamed about changing the operator to '<>' and solving the problem. But i never imagined that the find function is depending upon an object call findformat and its properties like interior and fill format like all. Actually it first assigns the object with fillcolour constant before calling the find function thus i cant assign a value with '<>'. I still believe there must be a smarter way other than looping each and every cell of the workbook. Thanks in advance.

[Bob Phillips]

Not that I know of, unless you know the criteria for colouring and test that.

[Simon Lloyd]

Would this help start you off?
Sub list_filled_cells()
Dim MyCell As Range, msg As String
For Each MyCell In ActiveSheet.UsedRange
If MyCell.Interior.ColorIndex <> xlNone Then
msg = msg & vbLf & MyCell.Address
End If
Next MyCell
MsgBox "Filled cells found at" & vbLf & msg
End Sub

[mikerickson]

How about using FindFormat .Interior.ColorIndex = xlNone, counting those and subtracting?