New to Excel Macros - QUESTION

If you want to do this while the file is still open in Excel  you can do it with a global variable. Global variables are defined when Excel file is opened and stays in the memory till that file is closed. However if you need to do this even when you close and reopen the file there are several ways:

1. Create a workbook_open event in the Workbook (you can select workbook in the project explorer and write macro) so that it checks the already formatted cells. The stategy is all up to you. You can do it with a for-next, do-loop whatever you want. And write the value to a global variable. The downside is that if the unformatted row number is too much than file open time can dramatically increase.

2. You can do this operation before formatting all cells. You need to find the last row used and go up to find last formatted cell.

3. You can format all cells without checking whether is is formatted before or not. Only downside is that if the number of rows to be formatted is rather large formatting operation takes longer.

4. you can define a workbook_before close or before save event so that program format all the cells as you want before closign the document and you always be sure that last formatted row is the last used row. This will also increase the file saving and closing time.

5. you can create a veryhidden sheet. Add a sheet normally and change the visibility property of that sheet to very hidden in VBA IDE. And add the last formatted row number to a cell as value and read that value every time the macro starts. This page can be used to store other settings also.