Solved: Count highlighted rows

[christopherw]

I'm having trouble with some code. I need to count the number of rows that have font color -16777024 and that are highlighted 49407. Would anyone have any suggestions or answers on how to count these?

[christopherw]

I failed to mention that this is in VBA

[CodeNinja]

Sub testColor()
Dim l As Long
Dim lCount As Long

lCount = 0
For l = 1 To Sheet1.UsedRange.Rows.Count
If Sheet1.Cells(l, 1).Font.Color = 192 And Sheet1.Cells(l, 1).Interior.Color = 49407 Then lCount = lCount + 1
Next l
MsgBox ("Total of " & lCount & " rows highlighted in 49407 and font color -16777024.")
End Sub

[Bob Phillips]

Dim cell As Range
Dim cnt As Long

For Each cell In Selection

cnt = cnt - (cell.Font.Color = 192 And cell.Interior.Color = 49407)
Next cell

MsgBox cnt

[christopherw]

Thanks to the both of you. I keep getting 0 returns and there are many lines that meet these criterion. The workbook has multiple sheets and the sheet I'm trying to do the count is called "Holds" and I want it to start at row 2 and count. There could be 200 lines on one and up to 5,000 lines on another, so I don't need to limit the count. Any thoughts?

[snb]

Only one: post a sample workbook without any data (since it's the formatting you are after)

[Bob Phillips]

My version works on the selected cells, but CodeNinja's should process the whole sheet, but the first row of the data. My guess is that it is not the first row but you haven't told us which.

[CodeNinja]

My version works on the selected cells, but CodeNinja's should process the whole sheet, but the first row of the data. My guess is that it is not the first row but you haven't told us which.
Mine actually handles the first Column of every row on sheet1, but Christopherw said he wanted to count rows, so I figured the entire row would have the highlighting thus the first column having the highlight would solve the count of rows... Guess my assumption was not correct...

Christopherw, if you cannot modify the code to suit your needs, you need to provide a sample for us to work on.

[christopherw]

OK I attached a sample for you to see. The sample size is small in comparison to the usual report. Since the highlighting stops at W then perhaps it should be A & i W & i? Sorry just trying to figure this out.

I appreciate all your help

[snb]

Sub snb()
For Each cl In Columns(1).SpecialCells(2)
If cl.Font.ColorIndex & "_" & cl.Interior.ColorIndex = "3_44" Then j = j + 1
Next
MsgBox j
End Sub

[christopherw]

I need the count to return the rows with interior color 49407 and font color as -1677702. I put the sample of the worksheet in this forum. I could not get snb's code to work.

[snb]

cfr. the attachment.

You can easily adapt it to every other color.

[christopherw]

the code doesn't work in the attachment. it doesn't keeps debugging on:

For Each cl In Columns(1).SpecialCells(2)

I already have the cells highlighted the color I want. I just want to count those lines with the colors.

[CodeNinja]

Christopherw,
I just got around to looking at this (sorry for the delay). I opened your sample and put my code (above) in a new module and ran the code, and I got a messagebox saying there are 2 rows with those lines and colors.

I believe that is what you wanted. If it is not, what would be the response you expected?

[christopherw]

It does work. I have a workbook with multiple worksheets. I need this to run on the worksheet called "holds". I'm sorry for my stupidity on this, but where would I put that in the code?

[CodeNinja]

OH! Wherever you see "Sheet1" in the code, replace that with "Sheets("holds")

so:

Sub testColor()
Dim l As Long
Dim lCount As Long

lCount = 0
For l = 1 To Sheets("hold").UsedRange.Rows.Count
If Sheets("hold").Cells(l, 1).Font.Color = 192 And Sheets("hold").Cells(l, 1).Interior.Color = 49407 Then lCount = lCount + 1
Next l
MsgBox ("Total of " & lCount & " rows highlighted in 49407 and font color -16777024.")
End Sub

[christopherw]

SWEET! Thanks so much. You made my weekend much better.

Prost!

[CodeNinja]

Glad to be of help. If you are satisfied with this answer and need no more help on this subject, please mark the thread solved (At the top where it says thread tools, you can drop down and mark it solved).

Have a great weekend.