+ Reply to Thread
Results 1 to 13 of 13

Get Total of Specific Background Colored Cells Pt. 2

  1. #1
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    20

    Get Total of Specific Background Colored Cells Pt. 2

    This question is building off the elegant solution provided by Alan found in my previous post.

    I was trying to modify his code as my excel spreadsheet expanded to:

    2021-04-19_16-41-33.png

    How to automatically get the sub-totals from each blocks (shown in P11-P13, AB11-AB13, P19-P21 and AB19-AB21) then get the overall total to show up in Column E.

    The original VBA code written by Alan is:

    Please Login or Register  to view this content.
    Please note that I have also cross-posted this question on Mr. Excel but due to my limited posts on this forum I cannot attach the actual link. The post on Mr. Excel is #post-5678201

    https://www.mrexcel.com/board/thread.../#post-5678201
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 04-20-2021 at 11:21 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Welcome (back) to the forum.

    Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

    But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

    Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

    1) if you have cross-posted, and

    2) the URL of the cross posts.

    That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Glenn



  3. #3
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    20

    Re: Get Total of Specific Background Colored Cells Pt. 2

    I put the link to the post on Mr. Excel in quotes.

    Thanks for making me aware of this requirement!

    Sincerest apologies!
    Last edited by jimjones1958; 04-20-2021 at 10:43 AM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Thanks. The link is now live. Open for anyone to help.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,534

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Will the area of the ranges always be the size you are showing in your example. Are the range areas fixed or dynamic?

  6. #6
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    20

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Hello,

    The range area and ranges are fixed.
    Last edited by alansidman; 04-22-2021 at 03:09 AM.

  7. #7
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    20

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Hello Alan,

    I did not get the code if you placed it here, but would making the ranges dynamic easier? Thanks!

    I've been trying all night and could not get it to work properly.
    Last edited by alansidman; 04-22-2021 at 03:09 AM.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,534

    Re: Get Total of Specific Background Colored Cells Pt. 2

    I thought I had a solution for you with fixed ranges, but it turned out to be more complicated and complex than I was able to figure out. I don't have a solution for you when there are more than one table that needs to be summarized and totaled. I have posted alerting others to have a look/see. Patience may need to be the word today.
    Last edited by alansidman; 04-21-2021 at 01:50 PM.

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,325

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Hard coded blocks and ranges:
    Please Login or Register  to view this content.
    Ben Van Johnson

  10. #10
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    20

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Hello Ben,

    Thank you for your elegant solution! I sincerely appreciate it!

    I can't believe it was done is just those few lines of code! Thank you!

    One extra requirement: How to also get the sub totals to show up from each block?

    I was thinking of using this:

    Please Login or Register  to view this content.

    Thanks again! Sincerely appreciated!
    Last edited by alansidman; 04-22-2021 at 03:08 AM.

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,534

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding. I have removed the extraneous quote.

  12. #12
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,325

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    05-20-2020
    Location
    USA
    MS-Off Ver
    10
    Posts
    20

    Re: Get Total of Specific Background Colored Cells Pt. 2

    Thank you! Works perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Get Total of Specific Background Colored Cells
    By jimjones1958 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2021, 05:42 PM
  2. Count Colored Cells that contain Specific initials
    By hadydea in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-14-2020, 09:57 AM
  3. Replies: 1
    Last Post: 11-09-2013, 02:43 AM
  4. Please help! Counting values in specific colored cells
    By dilemmax in forum Excel General
    Replies: 3
    Last Post: 02-08-2013, 01:13 PM
  5. [SOLVED] Creating Total Between Colored Cells
    By thesasman2012 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-02-2013, 09:54 AM
  6. Replies: 5
    Last Post: 09-22-2012, 09:53 AM
  7. Chart with colored background
    By Barb Reinhardt in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-27-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1