Totally data depending on colour of boxes

sinternational

New member
Joined
Mar 7, 2019
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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
 

Attachments

  • FIVERR.xlsx
    14.4 KB · Views: 13
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
 
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
 
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
 
That works great...Thanks

If I wanted to add two other colours would it be difficult?
 
No. Just add an ElseIf statement and declare new variables. Follow the same basic setup that I have provided for two colors.
 
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
 
The code line which defines lr (the last row) is a variable. It determines the last line for the code to work.
 
Back
Top