Count rows by colour

Topic

New Reply

I am sorry it you are seeing this twice as it did not appear last time
I am using this function in Excel 2010 to try to count rows coloured yellow, but it always returns 0.
Any ideas please
Braddy

Function CountYellow()
LastRow = Range(“A65000”).End(xlUp).Row
CountYellow = 0
For x = 1 To LastRow
Debug.Print Rows(x).Interior.ColorIndex
If Rows(x).Interior.ColorIndex = 6 Then CountYellow = CountYellow + 1
Next x
End Function

Reply | Quote

Replies

A few questions and comments:
1) the Color Pallette is customizable per workbook. Is a ColorIndex of 6 = “yellow” = 65535 = vbYellow in the workbook?
2) the entire row must be colored the same color for the function to return the correct count. If any cells in the row have a different color, they will not be counted
3) There are many of the millions of colors that look “yellow” but are not = 65535. Are you sure they are this color?
4) the code depends on any rows counted being in the range of rows that Col A has something in. Does Col A have entries in it and does the last one match the rows that are colored?

The last one concerns the “generality” of the function. 65000 was never the last row in XL. Pre-XL2007 it was 65536 which may not have been much of an issue. In XL2007 and XL2010 it is 1048576. To make it more general for all spreadsheets that have Col A filled in use:
LastRow = Cells(activesheet.rows.count, 1).End(xlUp).Row

If nothing else, post an example workbook that we can examine…

Steve

Reply | Quote

Hi Steve
Thanks for the prompt reply, I corrected the differences you pointed out, to start with the whole row wasn’t coloured, and I changed 65000 to1048576 and it now works.
I did notice that if I add row it doesn’t auto update is there a short cut key to update?.

Thanks again
Braddy

Reply | Quote

I did notice that if I add row it doesn’t auto update is there a short cut key to update?.[/quote]

You can add the line:
Application.Volatile

to the code and it will recalculate whenever teh workbook recalculates. You can pres to force a recalculation if a change does not trigger a recalc of the workbook

Steve

Reply | Quote

Braddy,

If you use a dynamic range name it will update automatically.
Of course you’ll need to modify it to use the appropriate last row value. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi There

Thanks for the reply, I don’t have the skill to understand what you are saying. sorry.
Regards
Braddy

Reply | Quote

Braddy,

I’ve been playing with this for a while now and here’s what I found out.
1. Reformatting does NOT cause a recalc! Thus if you apply formatting your count will not change until a recalc is forced.
2. Deleting a formatted row will cause a recalc.

Attached is a workbook which uses a dynamic range name {see Insert->Names->Define} to set up the range that will expand and or contract as you insert/delete rows. It also has a recalc button that forces a recalculation.

I’ve also redone the function to make it generic, e.g. you can pass any color number to it not just yellow.

Note: My function only checks Col A for color. You can adjust if you want to check for whole rows but be ware of the comment made earlier about the whole row being colored.
I hope this explains my earlier post and helps in your journey with Excel.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RetiredGeek

Thank you very much for your reply and information, that’s excellent.
A big thank you to all for the replies
Regards
Braddy

Reply | Quote

You already have answers to your question but I would just add that colour is not data and should not be used as such. If you use data to set the colours rather than trying to do it the other way round, your life will be much easier (in an Excel sense anyway).

Reply | Quote

Hi Rory

I hear what you say, only it’s not my spreadsheet it sent to me by the creator, however I may have,as you say other alternatives

Regards
Braddy

Reply | Quote