COUNTING A HIGHLIGHTED CELL

[MONNGO69]

I am very new to excel.
I have a list of products that my staff highlights and then forwards to me for approval....my question is ...is there any way that i can have the highlighted cells be counted automatically?....

say i highlight 15 cells in red,

can excel count all the highlighted cells?

[Todd Bardoni]

How about:-

Sub Test()
    For Each cell In Sheets("Sheet1").UsedRange
        If cell.Interior.ColorIndex = 3 Then i = i + 1
    Next cell
    MsgBox "There are " & i & " cells in red.", vbInformation
End Sub

[MONNGO69]

UHHH,,,I copied and pasted that ,but it did not work..or i donot know what i am doing,,,lol

i am banking on the later....can you explain it in remedial terms...sorry i am excel illiterate.....

[Todd Bardoni]

Try this:-
Hit Alt+F11 to display the VB Editor. Then go to Insert/Module and paste this code:-

Sub Test()
    x = ActiveSheet.Name
    i = 0
    For Each cell In Sheets(x).UsedRange
        If cell.Interior.ColorIndex = 3 Then i = i + 1
    Next cell
    MsgBox "There are " & i & " cells in red.", vbInformation
End Sub

[Corticus]

Hi MONNGO69,

By doing what tbardoni suggested you can paste the following function into a module:

Function ct_fill(rng As Range) As Integer

    Dim cl As Range

    ct_fill = 0
    
    For Each cl In rng
        If cl.Interior.ColorIndex <> xlNone Then
            ct_fill = ct_fill + 1
        End If
    Next cl

End Function

and use like this:
******** ******************** ************************************************************************>

Microsoft Excel - count_fill.xls ___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
B12 =
	A	B	C	D
1	*	Entry	*	*
2	*	Entry1	*	*
3	*	Entry2	*	*
4	*	Entry3	*	*
5	*	Entry4	*	*
6	*	Entry5	*	*
7	*	Entry6	*	*
8	*	Entry7	*	*
9	*	Entry8	*	*
10	*	Entry9	*	*
11	*	Entry10	*	*
12	shaded*entries:	4	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hope this helps,

[ThreeOfPentacles]

go to http://www.xl-logic.com/pages/vba.html
and download count_colors.zip

Regards
J (who is equally excel challenged)

[Iridium]

Also look at

http://www.cpearson.com/excel/colors.htm

HTH

[MONNGO69]

Hey all thanks for the help.

But when i try any of them i get a compile error expected end sub?????


any ideas?...

or am i doing it wrong?...

[Nimrod]

Sub CountColor()
For Each Cel In Cells.SpecialCells(xlCellTypeConstants, 3)
If Not Cel.Interior.ColorIndex = 0 Then Count = Count + 1
Next Cel
pt = MsgBox("The number of cells highlighted is : " & Count)
End Sub

TO INSTALL:
1. Select the sheet you would like to count
2. Right click on the sheets "Name Tab"
3. Select "veiw code" from drop down menu
4. VBE window will open
5. Paste the above code into the VBE window and close window

[Nimrod]

VERSION 2:

This version error traps if no cells have any values

Sub CountColor()
On Error GoTo NoCount
For Each Cel In Cells.SpecialCells(xlCellTypeConstants, 3)
If Not Cel.Interior.ColorIndex = 0 Then Count = Count + 1
Next Cel
pt = MsgBox("The number of cells highlighted is : " & Count)
Exit Sub
NoCount:
pt = MsgBox("No cells found to count")
End Sub