VBA for Excel Formatting / Multiple If Statements / Highlight

[leo38cheng]

Hi All,

New to the forum, and very glad I found this community. I am interesting in learning VBA for self-educational/work purpose, and hope you guys can help me.

*I've attached the spreadsheet I am working on, and was wondering if there is a Macro for the following functions, in Order.

Sample2-REVISED.xls

#1) Need to format ALL cells, to row height 12.75 , zoom to 75% , and Adjust Columns to Autofit - So I wrote the below in VBA

Please Login or Register  to view this content.

#2) I need to delete all ROWS with Purchase Date 60 days from "today's date" so I wrote the below:

Please Login or Register  to view this content.

#3) I need to highlight entire row, with Paid = " Y" so I wrote the below. *Is there anyway to adjust this formula, so that it highlights the whole row, up until the last column with data? How about up until the last column with data + 1 more column? * THANK YOU.

Please Login or Register  to view this content.

*Inquiries where I need help*

-Need Macro/VBA to look in Client column, and highlight/bold the row that contains the word "LEO", so if you referring to the file attached, there is also KhanLeo and I would like to have that one picked up too.

-Need Macro/VBA, if first 2 character of the invoice starts with 99, delete the row.
-Need Macro/VBA, if the 6th character of the invoice starts with 9, delete the row.

-Need Macro/VBA to change column J "Days between Purchase Date/ Invoiced Date" to Numbers, as some of them are currently "Numbers Stored as Text"

-Is it also possible to create a Macro/VBA formula, to do the following:
*If client type is in the range of 52000 to 53999 AND has a discount code of FAM2 OR 805P AND Days between purchase date and invoice date is 7, then delete row, if not leave it alone and do nothing.
*If client type is in the range of 58000 to 58999 AND discount code cell is BLANK AND the Days between purchase date and invoice date is 90, then delete row, if not then leave it alone and do nothing.
*If client type is in the range of 58000 to 58999 AND has a discount code of FAM2 OR 805P AND Days between purchase date and invoice date is 30, then delete row, if not leave it alone and do nothing.

*I would like to have one big macro, instead of separate macros... is that possible?

*I will try to do the others myself, and will seek help if need. THANK YOU SO MUCH FOR ALL YOUR HELP.

Sample2-REVISED.xls




*MY CODE SO FAR*

Please Login or Register  to view this content.

Sample2-REVISED.xls

[leo38cheng]

Good Morning All,

Would like to know if someone can help with my VBA formulas.

Thank you so much.

[leo38cheng]

bump for help.

Thanks.

[leo38cheng]

*bump for help. Thank you.

[leo38cheng]

*can a member please assist? Thank you.

[protonLeah]

You need to use code tags to wrap EACH of the Sub..End Sub code blocks you've posted. The workbook you posted has no VBA code included. That means trying to copy from the current post to the workbook and editing to isolate the code. The code tags make it easier for anyone wishing to help...

[protonLeah]

Solution deleted, waiting for code tags

[leo38cheng]

Hi All,

*bump for help. File and post has been revised. Thanks!

-Leo

[protonLeah]

1. A far as #3 is concerned, you don't need a macro for that. Use Conditional Formatting. Select entire range of columns A - I or J and apply: =$H1="Y"
2. For

-Need Macro/VBA to change column J "Days between Purchase Date/ Invoiced Date" to Numbers, as some of them are currently "Numbers Stored as Text"

Why not use a formula in column J: =IF(G2<>"",INT(G2-D2),"") formatted as number.

3. My personal opinion is that one big macro is not that great. I would keep the smaller separate macros and CALL them from a Main macro, for example:

Please Login or Register  to view this content.