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.

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 ...).

Mike K wrote:

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

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

"Mike K" wrote:

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.

Billy, this worked perfectly! Much appreciated!!!

"Billy Liddel" wrote:

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

"Mike K" wrote:

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.

Mike

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

Regards
Peter

"Mike K" wrote:

Billy, this worked perfectly! Much appreciated!!!

"Billy Liddel" wrote:

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

"Mike K" wrote:

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.

Mike

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

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

EditFindOptionsFormat. 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

On Tue, 8 May 2007 12:38:00 -0700, Mike K <Mike
wrote:

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.