I need a VBA to distinguish Paid(Black Text) Unpaid(Red Text)

I'm very new to coding, and all that goes with itCustomer List - Copy.xls

I'm trying to make my life a little easier.
currently I move all my data (Red Text) from my spread sheet to the invoice of each customer manually.

If my customers pay there bill I change the text from RED to Black, that lets me see quickly that it is paid
at the end of the month I go through and type all the owed bills (red text) into a invoice on a word file.

is there anyway to wright a code to automatically place the red text onto a invoice?

Attached is what I have been working on with a new Invoice created in excel

Thanks in advanced!

Would you like all of the red data summed up, or would you like a separate line saying what month each amount comes from?

I would like to help you automate this as much as possible, would you like that?

I'm sorry, I went out for a bit. I think I went a bit of a different way with this. Try downloading this Customer List - Copy.xls. I have set it up so you can just choose a customer from the list on the invoice cell B8. It will populate the invoice from the sum sheet.

Hope it works, let me know.

Regards,
Everstrivin

The code that makes this happen 'lives' in sheet3 (Invoice). Go into the visual basic editor and you will see the name of the workbook and under it you will see the names of the sheets and then below that a module. Double click on any of the objects to see the code. Code can 'live' in a few places: each sheet can have code, the workbook and modules can be a good location for code which is meant for more than one sheet, userforms will have code to deal with the controls on them, and there may be a couple of other places beyond those.

The code that runs this is based on the 'change' event for sheet3. Anything that changes on sheet3 will cause the code to run, the first if statement then checks to see if the cell with the customer name is the one that changed.

Please Login or Register  to view this content.

A 'With' statement allows you to not have to keep typing out the full object you are referring to. Within a 'With' statement you just need to use a period and everything in the statement then refers to the object refered to in the 'With' statement. You can see some '.range(' and sme just 'range(' statements. Within the 'With' statement the 'range(' statement without the preceding period will refer to the default object, in this case the default object is sheet3 because the code 'lives' in sheet3. So all '.range(' statements deal with sheet4 whereas all 'range(' statements deal with sheet3. Likewise '.cells(' deals with cells on sheet4 whereas 'cells(' deals with cells on sheet3.

I'm going to assume that if you don't know what 'if' statements and 'for' loops are you can either look them up or figure them out.

The part of the code that answers your question is:

Please Login or Register  to view this content.

As for what is actually on the sheet, I changed the formulas in the invoice so it would calculate the number of pickups based on the 5 dollar charge and the amount being invoiced. It does all seem a bit backwards, I think it might make more sense for someone if you just recorded in a table the pickups and the cost per pickup and whether or not it had been paid yet or not, but your way seems to be pretty simple and probably works well for your application.

The customer list dropdown is just a common 'dynamic list' in a 'named range' used with 'data validation'. If you look at the formula for the named range 'custs' you will see the following formula:

Please Login or Register  to view this content.

Offset is a great formula for referring to one or a group of cells based on the location of another, here we base our search for the cells on cell Customer_List!$B$4, we shift no rows, we shift over 0 columns, we make the range of cells as high as there are a count of customers and we want a range only one column wide. Sumproduct is very useful but somewhat advanced, but basically compares all of the cells contenrs in the range from b4 to b10000 to see if anything is in each cell. Multiplying by 1 then changes true and falses with 1's and 0's. You can use the 'evaluate formula' to see how this one works if you paste it into any blank cell. It will evaluate to an error but 'evaluate formula' will help you see how it works. I've made it possible effectively for you to have 10000 customers, if you need more you will need to change this value and the vales in the vba. I didn't, but you may want to make it easier to get customers into the sum sheet by making 'data validations' below the names you already have on the sum sheet and put as your list '=custs'.

I also went into the advanced options of the workbook to make 0's not show. I just thought it was better than having to keep putting

Please Login or Register  to view this content.

I think that would pretty much do it. If you have any further questions, just let me know and please don't forget to mark this thread as solved.

delete Message to shorten post

delete Message to shorten post

Thanks for your help, I wish I could wrap my head around all that you told me. With no coding background at all its just words, some I understand, and a lot more I don't. I'm going to keep playing with it and trying to get it to work on my updated version because I would really like to have 2 separate bills per print page, because I use a paper cutter and cut each peace in half. Saves on paper
witch saves trees.

Would I have to keep the Customer Number, or could I use other parts of the table to locate data needed?

also do I need the Customer_List sheet, or could I combine that with the Sum sheet, and have both the monthly data and customer information all on one sheet, the reason I ask is when I want to add new customers I would really like to add them in different locations, not just the end of the sheet, because right now my customers are in order from 1st to last as I pick them up. As I add new customers I would like to place them in the list in order of when I pick them up.

Customer List Copy.xls