H
Hari
Hi,
I have a piece of code in a file which Iam distributing to lots of other
users.
In this whatever data they fill need to be internally consistent otherwise
the file doesnt save ( using workbook_beforesave). Along with that the file
also fills all those inconsistent cells with a yellow pattern.
The user will then work on those cells, correct them and try saving the file
again.
Now in the code just before actual consistency check starts the first line
of code in workbook_beforesave is to fill all the cells in the range with no
color pattern so that the checking may start from a clean slate.
For that I have the following :-
For removepreviouscolor = 2 To 1500
For p = 2 To 38
Sheets(sheetName).Cells(n, p).Interior.ColorIndex =
xlColorIndexNone
Next p
Next removepreviouscolor
The problem is it takes a long time ( I think because of 1499 * 37 cells ).
Now the there are 2 sheets in which my consistency check happens. And in
both the sheets I run the above code for no color. In both the sheets it is
the cells B2:AL1500 in which this code is run.
In the code rather than using 2 FOR statements ( which makes it run for
atleast 2 mins or so) is there any other way of directly mentioning the
range B2:AL1500 and change the color to no color in all the cells.
Regards,
Hari
India
I have a piece of code in a file which Iam distributing to lots of other
users.
In this whatever data they fill need to be internally consistent otherwise
the file doesnt save ( using workbook_beforesave). Along with that the file
also fills all those inconsistent cells with a yellow pattern.
The user will then work on those cells, correct them and try saving the file
again.
Now in the code just before actual consistency check starts the first line
of code in workbook_beforesave is to fill all the cells in the range with no
color pattern so that the checking may start from a clean slate.
For that I have the following :-
For removepreviouscolor = 2 To 1500
For p = 2 To 38
Sheets(sheetName).Cells(n, p).Interior.ColorIndex =
xlColorIndexNone
Next p
Next removepreviouscolor
The problem is it takes a long time ( I think because of 1499 * 37 cells ).
Now the there are 2 sheets in which my consistency check happens. And in
both the sheets I run the above code for no color. In both the sheets it is
the cells B2:AL1500 in which this code is run.
In the code rather than using 2 FOR statements ( which makes it run for
atleast 2 mins or so) is there any other way of directly mentioning the
range B2:AL1500 and change the color to no color in all the cells.
Regards,
Hari
India