how to count number of highlighted cells in column

[Guest]

Within an existing worksheet I have a column of data containing cells that
are either highlighted in yellow (manually highlighted) or not highlighted in
yellow. I need to sum how many cells are highlighted in yellow and how many
cells are not highlighted in yellow. I'm running MS Office Excel 2003 SP2.

 

[Dave Peterson]

You're going to have to use some VBA.

Chip Pearson explains it:
http://cpearson.com/excel/colors.htm

As alternative.
Add an extra column and put an indicator in that column (X). Then use
format|Conditional formatting to make it look pretty. But use that indicator
column in you calculations (=countif() or = sumif() or ...).

 

[Guest]

Mike

With xl2003 it has to be done with VB. Here's one way, you have to select
the range then run it.

Sub TestColor()
Application.ScreenUpdating = False
x = Selection.Cells.Count
For Each c In Selection
c.Select
If Selection.Interior.ColorIndex = 6 Then
Count = Count + 1
End If
Next
Application.ScreenUpdating = True
MsgBox Count & " coloured cells"
MsgBox x - Count & " Non coloured Cells"
End Sub

Regards
Peter

 

[Guest]

Billy, this worked perfectly! Much appreciated!!!

 

[Guest]

Mike

You're welcome. Thanks for the feedback and rating.

Regards
Peter

 

[Gord Dibben]

Mike

Without using VBA..........

Select the entire range of colored/non-colored cells.

Edit>Find>Options>Format. Click the dropdown arrow and hit Format.

Select the yellow color from the patterns dialog and OK.

Then Find All.

The first yellow cell will be highlighted in the Find dialog.

Scroll down to bottom and hit Shift + click to select all in dialog box.

In the status bar right-click and select Count.


Gord Dibben MS Excel MVP