Archiving Rows on Excel using VBA

[Corrina]

Hi,
This is my first time posting so please be gentle!

I have two sheets relating to invoices named Current and Paid.

I'd like to do a few things

1. When I enter data into the sheet named Current, it highlights the row to remind me it's to be paid.

2. When I enter a date into the the Paid column of the worksheet (column 9):

a) the highlight is removed
b) the row is moved to the next available row in the worksheet named Paid and the blank row created in the worksheet named Current is deleted (data moved up).

I can highlight and remove the highlight using conditional formatting using COUNTIF however am unsure of what to write to read a date (1/1/17) rather than text.

Any assistance will be greatly appreciated.

Many thanks,
Corrina

[Bob Phillips]

Corrina, you would be far better off to have just one table with all items. They are all invoices, they are just in a different state of not paid or paid, so splitting just adds unnecessary complexity to your spreadsheet.

You can identify the paid because they will have a date. If you add filters to the columns, you can filter on anything, customer, amount, and of course, paid invoices.

[mdmackillop]

I can highlight and remove the highlight using conditional formatting using COUNTIF however am unsure of what to write to read a date (1/1/17) rather than text.

Make sure your column is formatted as date e.g. NumberFormat = "dd/mm/yyyy"
Select the column and use DateValue in the formula.

For more on Conditional Formatting, visit Contextures

[Corrina]

Thank you xld and mdmackillop will try it and see how I go

[Bob Phillips]

Do let us know what you try and how it goes. Don't just accept whatever you get, there is always round 2 :-).

[mykal66]

Hi Corrina.

I have a test workbook that some of the guys on here helped me with in the past that will move the rows to the appropriate worksheet once marked as paid - attached

I've used it on several workbook at work and the code is simple to follow

Hope it helps

Mykal

Move and delete.xlsm

[Corrina]

Hi Corrina.

I have a test workbook that some of the guys on here helped me with in the past that will move the rows to the appropriate worksheet once marked as paid - attached

I've used it on several workbook at work and the code is simple to follow

Hope it helps

Mykal

Move and delete.xlsm

Wow mykal66 (66 is a good year by the way!)

I've copied my data into the spreadsheet you attached and it works like a charm!!

Many thanks
Corrina