+ Reply to Thread
Results 1 to 4 of 4

Get Total of Specific Background Colored Cells

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

    Get Total of Specific Background Colored Cells

    Hello,

    In the attached spreadsheet, I would like to get the total of the different colored cells:

    2021-04-19_13-56-53.png

    I have tried using the GET.CELL(28,xxx), however, this approach cannot work because I need to have the "cell color number" appear in the respective colored cell.

    How can I automatically get the total of the different colored cells in that "Total" column?

    Sincerest Thanks for all your time and help!
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Get Total of Specific Background Colored Cells

    Here is a VBA solution. I put the totals in column E. A note of information, the color index for cell D7 should be 40

    Please Login or Register  to view this content.
    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    5
    GetColor Total
    6
    R
    No clip
    35
    23
    7
    R
    Light Clip
    40
    5
    8
    R
    Heavy Clip
    38
    12
    Sheet: Sheet1
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

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

    Re: Get Total of Specific Background Colored Cells

    Hello Alan,

    Your code works perfectly.

    As a side note, is a VB script the only way to go here? That is, can this be carried out via a formula?

    Also, can you tell me why I keep getting "36" instead of "40" like you have shown in your solution?

    Thanks again!
    Last edited by jimjones1958; 04-19-2021 at 04:05 PM.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Get Total of Specific Background Colored Cells

    Here is the code I used to determine the Index color.

    Please Login or Register  to view this content.
    I am not aware of a formula solution.

+ 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. Count Colored Cells that contain Specific initials
    By hadydea in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-14-2020, 09:57 AM
  2. Replies: 1
    Last Post: 11-09-2013, 02:43 AM
  3. Please help! Counting values in specific colored cells
    By dilemmax in forum Excel General
    Replies: 3
    Last Post: 02-08-2013, 01:13 PM
  4. [SOLVED] Creating Total Between Colored Cells
    By thesasman2012 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-02-2013, 09:54 AM
  5. Replies: 5
    Last Post: 09-22-2012, 09:53 AM
  6. Have blocks of blue colored cells ... want them surrounded by lavender-colored cells
    By rocket1406 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-01-2007, 03:59 PM
  7. [SOLVED] 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