PowerPoint VBA - For Loop and Array

Question

I am trying to replicate the below code with arrays, to avoid repeating the code 6 times. The code colours two boxes based on the value of one, in this case TueCum and TuePer - I also have WedCum, WedPer etc.

If Val(TueCum) >= Val(ProdTarg) * ((2 * Sixth) + SixthLim) Then
    TueCum.BackColor = &HFF00&
    TuePer.BackColor = &HFF00&
    ElseIf Val(TueCum) < Val(ProdTarg) * ((2 * Sixth) - SixthLim) Then
    TueCum.BackColor = &HFF&
    TuePer.BackColor = &HFF&
    Else
    TueCum.BackColor = &H80FF&
    TuePer.BackColor = &H80FF&
End If

I have got this far,

Dim Cum(0 To 5)
Dim CDay As Variant
Count = 1

Set Cum(0) = MonCum
Set Cum(1) = TueCum
Set Cum(2) = WedCum
Set Cum(3) = ThuCum
Set Cum(4) = FriCum
Set Cum(5) = SatCum

For Each CDay In Cum
    If Val(CDay) >= Val(ProdTarg) * ((Count * Sixth) + SixthLim) Then
        CDay.BackColor = &HFF00&
        TuePer.BackColor = &HFF00&
        ElseIf Val(CDay) < Val(ProdTarg) * ((Count * Sixth) - SixthLim) Then
        CDay.BackColor = &HFF&
        TuePer.BackColor = &HFF&
        Else
        CDay.BackColor = &H80FF&
        TuePer.BackColor = &H80FF&
    End If
Count = Count + 1
Next CDay

But I can't seem to get it to format both textboxes at the same time, I have tried having a second array, but can't get it right. So I need to make it colour TuePer and TueCum when it calculates TuePer, colour WedPer and WedCum when it calculates WedPer, etc.

d-stroyer · Accepted Answer · 2013-07-03 09:39:20Z

2

This should work:

Dim Cum(1 To 6)
Dim Per(1 To 6)
Dim Count as Integer

Set Cum(1) = MonCum
Set Per(1) = MonPer
Set Cum(2) = TueCum
Set Per(2) = TuePer
Set Cum(3) = WedCum
Set Per(3) = WedPer
Set Cum(4) = ThuCum
Set Per(4) = ThuPer
Set Cum(5) = FriCum
Set Per(5) = FriPer
Set Cum(6) = SatCum
Set Per(6) = SatPer

For Count = 1 To 6
    If Val(Cum(Count)) >= Val(ProdTarg) * ((Count * Sixth) + SixthLim) Then
        Cum(Count).BackColor = &HFF00&
        Per(Count).BackColor = &HFF00&
    ElseIf Val(Cum(Count)) < Val(ProdTarg) * ((Count * Sixth) - SixthLim) Then
        Cum(Count).BackColor = &HFF&
        Per(Count).BackColor = &HFF&
    Else
        Cum(Count).BackColor = &H80FF&
        Per(Count).BackColor = &H80FF&
    End If
Next Count

edited Jul 3, 2013 at 9:39

answered Jul 2, 2013 at 12:30

d-stroyer

2,7262 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Conquistador Over a year ago

Hi @d-stroyer, thank you! That's great, one little change I had to make, which was If Val(Cum(Count)) >= Val(ProdTarg) Thank you again for your help!

Collectives™ on Stack Overflow

PowerPoint VBA - For Loop and Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related