MrExcel.com - Your One Stop for Excel Tips & Solutions

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

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

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

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

I highlight the entire row, color is "post-it note" yellow

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

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

I highlight the entire row. color is light yellow

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

I highlight the entire row. The color is light yellow

Posted by Juan Pablo on October 09, 2001 9:03 AM

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

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

Posted by Juan Pablo on October 09, 2001 9:17 AM

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

If the color is Light Yellow then use

10092543

instead of

65535

Juan Pablo

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

Thanks for your help...

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