Find and replace

[hntp]

I have an excel worksheet that is a customer aging report pulled out of our accounting software. I need help on the following things. I want to be able to create a macro to do the following things because it is pretty much the same each month when I pull the new aging report.

- I want to find all invoices that start with 3 numbers and end with "RT". Example: 123RT, 145RT, etc. I want to align all of these to the right.

- I would like to find all 6 digit numbers that start with 13 or 14 and put "MI-" in front of these numbers and align right.

- I would like to find each row that starts with a 6 digit number between 50000 and 60000 and highlight that row blue.

Thank you very much.

[NickyC]

Hi
try this macro
it assumes your invoices are in column 2 of the used range, your 6-digit numbers in column 3 and your row numbers to highlight in blue are in column 1 - adjust the variables to reflect the ranges you want to search

Please Login or Register  to view this content.

[hntp]

Thank you very much!

All info is in column 1. I changed the above to:

Please Login or Register  to view this content.

Everything seems to work except the customer row getting highlighted blue.

[hntp]

Ok I partially figured something out. In the line I want to highlight blue it looks like the example below all in one cell.

50225 ANNING-JOHNSON CO., (DW) Sally (A/P) (999) 999-9999

The macro that you gave wants to highlight cells if there is only a number in the cell. If the cell has any other characters it does not highlight the row.

For example I changed the "50000" to "1" to see what would happen and it highlighted the rows that had any number in column 1 less than 60000.

Thanks again for the help.

[NickyC]

ok
if all the numbers etc are in 1 column we can simplify the first bit
as it currently stands the part putting MI in front of 6-digit numbers will only work if the cell contains a number - do you want that to also pick up eg "123456ABC

Please Login or Register  to view this content.

[hntp]

Perfect!! Thanks!