Results 1 to 10 of 10

Thread: Totally data depending on colour of boxes

  1. #1
    Seeker sinternational's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Totally data depending on colour of boxes



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi
    In the attached file you have yellow and green boxes.

    I need the totals from the green and yellow boxes to be updated in their relevant boxes on the right.

    I might have to change the number of green and yellow boxes as I progress through the year.

    Hope someone can help.

    Thanks
    Attached Files Attached Files

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,635
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    are the cells colored manually?
    Thank you Ken for this secure forum.

  3. #3
    Seeker sinternational's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    HI
    Yes they are.

    Thanks

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    This code will work if you change the green to an identifiable color index. I was unable to determine the color index for the particular green you used. The yellow was a Number 6, but your green was indeterminable. I tried 10 and other colors based upon the chart here-->http://dmcritchie.mvps.org/excel/colors.htm

    Code:
    Option Explicit
    
    
    Sub coloredBoxes()
        Dim i As Long, lr As Long, j As Long
        Dim x As Long, y As Long, w As Long, v As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
    
    
        For i = 7 To lr
            x = 0: y = 0: w = 0: v = 0
            For j = 4 To 11
                If Cells(i, j).Interior.ColorIndex = 6 Then
                    w = Cells(i, j)
                    x = x + w
                ElseIf Cells(i, j).Interior.ColorIndex = 10 Then
                    v = Cells(i, j)
                    y = y + v
                End If
            Next j
            Range("L" & i) = x
            Range("M" & i) = y
        Next i
    End Sub

  5. #5
    Seeker sinternational's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Hi
    I assume I put this code into the visual basic ?

    I changed the green to a colour 4 from the table.

    Will this add up the total in say the yellow or green boxes so I have the total?

    Thanks for your help

  6. #6
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    Standard Module
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    Press Alt-F8 to open the macro list
    Select a macro in the list
    Click the Run button
    Will this add up the total in say the yellow or green boxes so I have the total?
    Yes

  7. #7
    Seeker sinternational's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    That works great...Thanks

    If I wanted to add two other colours would it be difficult?

  8. #8
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    No. Just add an ElseIf statement and declare new variables. Follow the same basic setup that I have provided for two colors.

  9. #9
    Seeker sinternational's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Hi
    Thanks for that. Can I just ask my last final question. If I wanted to increase the range downwards so more many boxes. Where would I change this in the script?

    Thanks

  10. #10
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    The code line which defines lr (the last row) is a variable. It determines the last line for the code to work.

Posting Permissions

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