Excel VBA Sum range until reached value, then use range sumed to change cell format

[Uriel Castillo]

Hello everyone.
This is my first time possting, and altough i have some expirience with Excel, i´m relative new to VBA, so i'll be very grateful if you can help with this.

I have a document who looks like this:

Cedis	Visits	$	%
P1	7​	$ 355	24.58%​
P3	2​	$ 319	22.10%​
P5	4​	$ 291	20.16%​
P6	2​	$ 158	10.95%​
P2	2​	$ 146	10.12%​
P0	1​	$ 124	8.62%​
P4	1​	$ 29	2.01%​
P7	1​	$ 21	1.46%​
Sum	20​	$ 1,443	100.00%​

The number of rows is always variable, but column D always has percentages, and the sum, excluding the lastrow, it's always 100%. Also, column D it´s always sorted from high to low value.

I want to create a Macro who, from D2, start to sum the values below until the sum it's equal or higher to 80.00 %, and once that condition it's met, change the interior color of that rows to green (RGB (0,255,0)), from A to D.
In my example, the cells who sum 80.00 % or higher are D2 to D6 (87.91%), and the interior color for the range from A2 to D6 will be green (RGB (0,255,0)).

Thank you in advance

 

[lrobbo314]

How about this?

Book1
	A	B	C	D
1	Cedis	Visits	$	%
2	P1	7	$355	24.58%
3	P3	2	$319	22.10%
4	P5	4	$291	20.16%
5	P6	2	$158	10.95%
6	P2	2	$146	10.12%
7	P0	1	$124	8.62%
8	P4	1	$29	2.01%
9	P7	1	$21	1.46%
10	Sum	20	$1,443	100.00%
Sheet3

Sub SP()
Dim r As Range:     Set r = Range("D2:D" & Range("D" & Rows.Count).Row - 1)
Dim c As Range
Dim u As Range
Dim TP As Double

For Each c In r
    If TP > 0.8 Then
        Exit For
    Else
        TP = TP + c.Value2
        If u Is Nothing Then
            Set u = c
        Else
            Set u = Union(u, c)
        End If
    End If
Next c


Set u = u.Resize(, 4).Offset(, -3)
u.Interior.Color = RGB(0, 255, 0)

End Sub

 

[Uriel Castillo]

Thanks a lot, it worked perfectly. I'm still learning to "read" what is happening inside the macro ?, so i can adapt it to future projects.
Thanks again

 

[Marcelo Branco]

This can be done using Conditional Formatting
Select A2: D9
Conditional Formatting> New Rule> Use a formula to determine ...
Insert this formula
=SUM($D2:$D$9)>0.2
Pick a format

M.