# Thread: Totally data depending on colour of boxes

1. ## Totally data depending on colour of boxes

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

2. Hi and welcome
are the cells colored manually?

3. HI
Yes they are.

Thanks

4. 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. 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?

6. 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. That works great...Thanks

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

8. No. Just add an ElseIf statement and declare new variables. Follow the same basic setup that I have provided for two colors.

9. 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. 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
•