Forum Discussion
New to Excel Macros - QUESTION
I have a three column table. I only want to reformat (say resize and red color) some of the rows. Say I create a table with 100 rows. Later I change the number to 95 rows or 105 rows. Is there any way to use the existing macro to reformat only those rows I have already reformatted (made larger and colored red) even though the row number may have changed? If this is possible? If so, please let me know how. Thank you very much in advance!
1 Reply
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.
