Hi all,
I have written the VB below to apply conditional formatting on a large selection of cells in Column B:
But, this causes the worksheet to hang as it applies it to 9996 cells all the time. Is there a way to smarten this up to apply to the whole of Column B, but only the cells that have values in them?
Please Login or Register to view this content.
Thanks in advance.
Maybe change your first line to work only with those cells that are affected (the 'target' cells of column B's range)
______________________Please Login or Register to view this content.
Some other tips to speed up the execution..
1. Use DIM to declare the data type of each variable. Otherwise they are considered to be type 'Variant' and require more processing.
2. Turn off screen updating at the start of your macro and restore it at the end. This can greatly improve the execution speed.
Please Login or Register to view this content.
Last edited by StuCram; 10-23-2015 at 01:47 PM.
If this has been helpful, please click on the star at the left.
In addition, if you are going to be using the Worksheet_Change event you probably don't need the loop which will dramatically speed things up and reduce errors
Please Login or Register to view this content.
If you are happy with my response please click the * in the lower left of my post.
Hi stnkynts
I think your "Next" statement should have been "End If"
- Stu
You are correct sir. Good catch.I think your "Next" statement should have been "End If"
Please Login or Register to view this content.
I made the changes suggested (or at least, I tried to!) but I get an error. Here's what I now have. The first part of code relates to a different thread I had open.
The error is:Please Login or Register to view this content.
Run-time error 91
Object variable or with block variable not set
on the line...
I'm guessing it's because I've not told that part of code what it's looking at (I used DIM on JobStatus, but then didn't mention it here?) but not sure how to correct - the suggested code differed in the two responses. Would you be able to help me a bit further? Thanks in advance.Please Login or Register to view this content.
Try changing these:
To:Please Login or Register to view this content.
Please Login or Register to view this content.
Many thanks all
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks