# Auto sum invoice amounts when invoices are still outstanding?

#### Loulax07

##### New Member
this is driving me crazy. i use an Excel sheet for accounting purposes for my business. basically the cells are from left to right- Invoice #, invoice date, invoice \$, date received. Before they're received, I highlight the cell of the invoice # in yellow to show me that it's outstanding and to follow up with the customers. What I want to do, at any one time, is show how much we have outstanding in open invoices and not sure how to do it.

One way I thought was to add another cell, with a pull down of either Yes or No. Yes= invoice paid, No= invoice open. I can then create an IF THEN statement or something that would only add that \$ value into a sum if it's still open or No was selected.
The other way I thought which I know is more complicated is to have it sum only when the invoice # is highlighted in yellow.

Any thoughts?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Logit

##### Well-known Member
.
.
I used two additional columns: F & G

In F2, paste this formula, then copy down:
Code:
``=IF(GetFillColor(B2)=6,"DUE", IF(GetFillColor(B2)<>6,"", ""))``
.
.
In G2, paste this formula, then copy down:
Code:
``=IF(ISNUMBER(SEARCH("DUE",F2)),D2,"")``
.
.
For demonstration purposes, in G31, paste this formula:
Code:
``=SUM(G2:G28)``
.
.
To make the formulas in column F work as desired, create a routine module in the VBE window and paste this code:

Code:
``````Option Explicit

Function GetFillColor(Rng As Range) As Long
GetFillColor = Rng.Interior.ColorIndex
End Function``````

Please let me know how you make out.

#### Loulax07

##### New Member
Thanks! I tried it but when I changed the fill color from yellow to none and inserted a date in column E, it didn't change the F column away from "DUE" or erase the dollar value in column G. Am I doing something wrong?

#### Logit

##### Well-known Member
.
.
Ok ... here is a different approach using a VBA macro.

Paste the following code into a routine Module. You activate the macro with a Command Button positioned on the Sheet.

Code:
``````Sub ChangeColor()Dim lRow
Dim MR
Dim cell
Dim OffSet
lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
If cell.Interior.ColorIndex = 6 Then
cell.OffSet(, 4) = "DUE": cell.OffSet(, 5) = cell.OffSet(, 2)
Else
cell.OffSet(, 4) = "": cell.OffSet(, 5) = ""
End If
Next
End Sub``````

It's also possible to further automate the process by placing this macro instead, into the Worksheet_Change event. When a cell in Col B is highlighted Yellow
the macro will fire. The same if the cell is voided of a fill color. The downside to this approach though, is every time you click the mouse anywhere on the
sheet you will see the cursor 'vibrating'. Could be annoying after awhile.

losing me here!

#### Loulax07

##### New Member
sorry that seems really daunting and have never done things like that before

#### Loulax07

##### New Member
wow, very cool thank you so much! now i just need to figure out how to copy all this to my master spreadsheet. any tips or it's relatively easy?

#### Logit

##### Well-known Member
These instruction may seem long and daunting ... but ... I've written them step by step so as to avoid confusion.
You can create a new workbook that is empty, and practice on it first if that would give you a higher comfort level
for the "real thing".

Right click the Sheet Tab at the bottom of your screen.

Click View Code and the Visual Basic Editor window (VBE) will appear

In the menu selections up top, click Insert / Module

The MODULE name will appear in the hierarchy tree located on the left side of the VBE

Double click the MODULE

On the right side is a larger window, all white in color.

Copy and paste the code into this window

Code:
``````Option Explicit

Sub ChangeColor()
Dim lRow
Dim MR
Dim cell
Dim OffSet
lRow = Range("B" & Rows.Count).End(xlUp).Row
Set MR = Range("B2:B" & lRow)
For Each cell In MR
If cell.Interior.ColorIndex = 6 Then
cell.OffSet(, 4) = "DUE": cell.OffSet(, 5) = cell.OffSet(, 2)
Else
cell.OffSet(, 4) = "": cell.OffSet(, 5) = ""
End If
Next
End Sub``````

Close the VBE by clicking the X in the Upper Right Corner

Click the DEVELOPER tab up top on the menu selections

Click INSERT / a small form appears with many controls. Hover the cursor over these
controls until you find BUTTON. On my system it’s the upper left corner of the small form.
Click the control once.

Move your cursor to an area on your worksheet where you want the button to appear. Left click and
the button will be 'pasted' to your worksheet. At the same time, another form will appear showing
all the macros contained in your workbook. Find the macro named ChangeColor, left click it, then
click OK. You have now assigned that button control to that macro.

Anytime you click the button, the macro will run.

You can edit the text on the button and change its size by right clicking the button once. The button
will be surrounded with resizing controls. You can resize the button by left clicking/hold on one of the small
dots and dragging the mouse.

You can edit the text by moving the cursor over the resizing controls until the cursor turns into
four small arrows. When you see the arrows, right click and select EDIT TEXT. Pressing the DELETE key
will remove the text - you can then type any text you want.

You can move the button by hovering the cursor over the resizing controls until you see the four arrows.
Left click the resizing boundary, hold the left mouse button down and drag the mouse where you want
the button to be located.

Easy peezy ?

Replies
4
Views
2K
Replies
65
Views
3K
Replies
6
Views
586
Replies
3
Views
760
Replies
1
Views
1K

1,191,314
Messages
5,985,939
Members
439,990
Latest member
amsa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back