Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Count rows by colour

    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
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    If you are a fool at forty, you will always be a fool

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Last edited by RetiredGeek; 2011-06-05 at 08:48.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi There

    Thanks for the reply, I don't have the skill to understand what you are saying. sorry.
    Regards
    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I did notice that if I add row it doesn't auto update is there a short cut key to update?.
    You can add the line:
    Application.Volatile

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

    Steve

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    If you are a fool at forty, you will always be a fool

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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).
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post
    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
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •