Is there a way to count the number of highlighted cells in a col?

[Guest]

I have a column with about 8000 cells, all with different words in each cell.
Some cells are highlighted, some are not. I would like to know how many
cells are highlighted. Is there a way to count them or sort by highlighted
versus non-highlighted?

 

[Bernard Liengme]

Chip has something about counting be colour; www.cpearson.com
best wishes

 

[trav]

I think this will work, just copy and paste it into a module, either
your personal or for the workbook,


Code:
--------------------

Sub count_colored()
Dim CountCol, IndexCol, currentrow, CurrentCount, RepStartRow As Integer
Dim sheetname As String
Dim endofreport As Boolean

sheetname = ActiveSheet.Name
CountCol = 4 'column that holds the colored cells
IndexCol = 5 'Column that holds number index
RepStartRow = 2 'row number where the report data starts
endofreport = False 'signal "end of report"
currentrow = RepStartRow 'current row number
CurrentCount = 0
'Selection.Value = Selection.Interior.ColorIndex
'Worksheets(sheetname).Cells(2, 2)

While endofreport = False
Do
If Worksheets(sheetname).Cells(currentrow, CountCol).Value = "" And _
Worksheets(sheetname).Cells(currentrow + 1, CountCol).Value = "" And _
Worksheets(sheetname).Cells(currentrow + 2, CountCol).Value = "" And _
Worksheets(sheetname).Cells(currentrow + 3, CountCol).Value = "" Then

Worksheets(sheetname).Cells(currentrow, CountCol - 1).Value = "Number of colored Cells"
Worksheets(sheetname).Cells(currentrow, CountCol).Value = CurrentCount
endofreport = True 'for blank cells mean end of page end of page
Else
If Worksheets(sheetname).Cells(currentrow, CountCol).Interior.ColorIndex >= 0 Then
'do some stuff
CurrentCount = CurrentCount + 1

End If



End If
currentrow = currentrow + 1

Loop Until endofreport
Wend
End Sub

--------------------


you can change the column number and row number,
also you can put the sum of the count anywhere you want, i just put it
at the bottom.

also if you want you can even count differnt colors.

let me know if this helped
Trav