Return Row info based on Column dates

[ddnron]

I'm creating a budget for my wife, and have been struggling with finding a code that I need.

When she pays a bill she highlights the dollar amount in light green background.
If the expense is has not been paided it has a white background.
If the expense has clear the bank she highlights it red.
So all outstanding expenses have a cell background color in light green or white.

I would like to attach a button (created already) that she would click and all outstanding expense for the correct date range would be displayed on a "balance" sheet starting in c7. Where the expense name and amount is displayed, listing all outstanding expenses using the next row function.

In cell "a1" I have the "now()" formula.

The dates go to column z.

As an example - if today where July 31, and rent and electric had a white or light green cell color, then it would be copied over to the Balance sheet, with the amount.

rent for Jun30 and gas for Jun 30th would not be copied, red background - they cleared the bank.
Gas for July 28th would not be copied because it has a red background - it cleared the bank.

I would appreciate any help on this. Thanks in advance for your help!

................................................column f..........g..............h
Monthly Planned Debits.................Pay Day......Pay Day.....Pay Day
................................................Jun-30-10....Jul-14-10...Jul-28-10

row 7....rent................................1000...........................100 0
row 8....Electric.............................................300.............. ....
row 9....Gas.................................40...............................4 0

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim NextLine As Long
Dim sh As Worksheet

Set sh = Worksheets("Sheet2")

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
sh.Range("A1:C1").Value = Array("Item", "Pay Day", "Amount")
sh.Cells.ClearContents
NextLine = 2
For i = 3 To LastRow

If .Cells(i, "A").Value2 <> "" Then

For j = 2 To LastCol

If .Cells(i, j).Value2 <> "" And _
.Cells(i, j).Interior.ColorIndex <> 3 Then

.Cells(i, "A").Copy sh.Cells(NextLine, "A")
.Cells(2, j).Copy sh.Cells(NextLine, "B")
.Cells(i, j).Copy sh.Cells(NextLine, "C")

NextLine = NextLine + 1
End If
Next j
End If
Next i
End With

End Sub
[/vba]

[ddnron]

Thanks for helping me with this XLD. I’m pretty ignorant when it comes to VBA. I know very little about it.
I think I need to explain myself a little better.
I have two worksheets one labeled “Balance Sheet”, the other is “Budget Plan”
On the “Balance” worksheet, I have a button, where I want to insert this code.
When button is selected – I want the code to return information from the “Budget Plan.”
“Balance” sheet should then display in Cells:
G11 is the row expense name – which comes for the “Budget Plan” worksheet column “B” starting in row 30:39.
G12 is the amount – which comes from the “Budget Plan” worksheet, and it has a dynamic range based on today’s date. It looks for the date (a1)which is equal to, or between dates in row 7, finds the two columns, then looks for cells greater than zero, with a light green or white background. Every cell that meets this criteria gets copied. If a cell is copied it copies the row heading from the same row, which is column “B”
I was able to run the code, it didn’t do anything, because it gave me a code error, from another code on the “Balance” worksheet. It had to deal with a line which stated value > 1. Could both codes be interacting (my ignorance) I have another button on the “Balance” worksheet that returns info from a worksheet called “register.” This code looks at one column, versus a moving column, and it uses the value > 1.
I know this is a lot of information, but I’m not sure how much you need to help me, so I could be over doing it.
Thanks again for all of your help on this.

[Aussiebear]

Please post a sample workbook. This will assist in elimating any errors that sometimes occurs when trying to explain what the User thinks may be the issue. To post the workbook, click on Go advanced, scroll down to Manage Attachments, and upload the workbook.

[ddnron]

I was wondering how to attach a worksheet - thank you - I'll do that.

[ddnron]

Okay - I attached the sample file. I have explainations in the file on the balance worksheet.

Any help would be wonderful. Thanks in advance

[Bob Phillips]

IN your original example, the header had a single date, now the workbook has two, so what is the payment date of the item?

You have lots of #REF to throw it all.

[ddnron]

I'm only going to use the single date, from the original. The two dates in the spreadsheet...the first row(14) is just reference. I want to use the ending dates(row 15)

I was only sharing a sample file and I had to keep it under 1.00 g for posting. So i removed alot of the columns. I was going to change the range when I added all of my coulmns back. So that is why there are alot of #ref. My coulmns goto "jg"

Thanks for your help on this XLD.

[ddnron]

Posted #8 twice, didn't know how to delete it. Sorry!

[ddnron]

Bump

[ddnron]

bumped

[ddnron]

Bump

[ddnron]

I was hoping someone could help me with this. As you can see from the dates, I have been trying for over a month, so I'm not in, any VBA class, or I would have failed the class.

I'm just trying to make one of my personal spreadsheets easier to use for the wife.

So if someone can help me that would be great.

If not...I'll finish reading my VBA for dummies book, and write it, I hope.