Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home



formula for counting highlighted cells (or lines)

Posted by Dana A on October 09, 2001 6:51 AM
I'm needing a formula to count how many lines (not necessarily cells) are highlighted within my worksheet.

For example: I keep a total of how many online queries are sent to me and how many are sent in by snail-mail. My online submissions are the highlighted lines and they change as more facilities go online. I need a running total of how many should be submitting online. So I need a formula that can count highlighted lines (the highlighted lines/cells don't always have data in them).


Do you highlight the entire row ? or just some parts ?, what color ? (NT)

Posted by Juan Pablo on October 09, 2001 8:21 AM


Re: Do you highlight the entire row ? or just some parts ?, what color ? (NT)

Posted by Dana A. on October 09, 2001 8:25 AM


Re: Do you highlight the entire row ? or just some parts ?, what color ? (NT)

Posted by Dana A. on October 09, 2001 8:26 AM
I highlight the entire row, color is "post-it note" yellow


Re: Do you highlight the entire row ? or just some parts ?, what color ? (NT)

Posted by Dana A on October 09, 2001 8:33 AM

I highlight the entire row. color is light yellow


I highlight the entire row. The color is light yellow

Posted by Dana A on October 09, 2001 8:36 AM


Re: Do you highlight the entire row ? or just some parts ?, what color ? (NT)

Posted by Juan Pablo on October 09, 2001 9:03 AM
Try this with this User Defined Function. To use it, create a new module in the VB Editor, and copy this code.

Now in excel, in the cell you
want the result (Has to be same sheet where you will do the "count"), type

=CountHL()

If you need it to be more flexible, just say so.

Public Function CountHL() As Long
Application.Volatile
Dim F As Long
Dim i As Long
F = Selection.Cells.SpecialCells(xlCellTypeLastCell).Row 'Because it's not always last WITH data
For i = 2 To F
If Rows(i).Interior.Color = 65535 Then CountHL = CountHL + 1
Next i
End Function

Juan Pablo


Re: Do you highlight the entire row ? or just some parts ?, what color ? (NT)

Posted by Juan Pablo on October 09, 2001 9:17 AM
If the color is Light Yellow then use

10092543

instead of

65535

Juan Pablo


Thanks for your help...

Posted by Dana a on October 09, 2001 10:56 AM

but I think I'm a little lost. What goes in the parenthesis? And how do I use all this?:


thanks for your help Juan




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.