Advertisement

Sum by Color vba special

Sum by Color

Below we will look at a program in Excel VBA that sums numbers by color.

Situation:

You have lent money to two twelve people. Some people have given it back (in black) and some still owe you money (red). You want to know how much money you still receive.

Sum by Color in Excel VBA

1. First, we declare two variables of type Integer. One named toReceive and one named i. We initialize the variable toReceive with value 0.

Dim toReceive As Integer, i As Integer
toReceive = 0

2. Second, we start a For Next loop.

For i = 1 To 12

3. We now check each number and only if the color of the number is red we add the number to toReceive.

If Cells(i, 1).Font.Color = vbRed Then
toReceive = toReceive + Cells(i, 1).Value
End If

4. Don't forget to close the loop.

Next i

5. Finally, we display the money still to receive. We use the & operator to concatenate (join) two strings. Although toReceive is not a string it works here.

MsgBox "Still to receive " & toReceive & " dollars"

6. Place your macro in a command button and test it.

Result:

Sum by Color Result



Credit for the above article goes to excel-easy.com

If you have any query or want any help related to excel feel free to join our Telegram channel and message us..

Channel link :

https://t.me/msexcelmaster101



Post a Comment

2 Comments

  1. Very Useful information. Please update with more vba coding.
    I have also subscribed to your Telegram Channel ;)

    ReplyDelete