Finding any coloured cell

December 12th, 2009, 11:23 AM

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.

stanleydgromjr · December 13th, 2009, 04:03 PM

prabha_friend,

Quote:

If there is a filled one then we have to report 'no' in a statement.

Where do you have to report the 'no' statements (in a cell in a certain column)?

The following code will find the cells with a background color in the active worsheet used range, and report in a message box the address of the cell.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

 
Option Explicit
Sub FindColor()
' stanleydgromjr, 12/13/2009
Dim c As Range
For Each c In ActiveSheet.UsedRange
  If c.Interior.ColorIndex <> xlNone Then
    MsgBox "Cell " & c.Address & " contains a background color."
  End If
Next c
End Sub

Then run the "FindColor" macro.