Excel VBA Worksheet Calculate running slowly

Hello all,

My question pertains to the Worksheet_Calculate VBA sub. I have the code below that I wish to execute every time a cell is "confirmed" within the worksheet.

Basically, I have the table range that spans columns A:I, with various field headers. Column A has numbers that either contain integers or values with decimals in them. The current code cycles through column A, and if the cell value contains a decimal, it is formatted as white fill with black bordering and font. If it does not contain a decimal, it is formatted as blue fill with white font, etc.

The current code works, but is very slow to run. I am hoping for some guidance on getting a more consolidated/streamlined block of code for this...something that may include a "For Each Cell in Rng" loop?

Any help is appreciated!

Please Login or Register  to view this content.

Very strange. Perhaps it's my machine...I just placed the code into a dummy workbook as well and ran it on <300 rows and still slow to respond (took 5 seconds for 22 rows before I ended it). Appreciate you taking a look nonetheless!

Hi,

Have you tried using conditional formatting instead?

Originally Posted by xlnitwit

Hi,

Have you tried using conditional formatting instead?

I have tried; it works, but I'm hoping to go the VBA route for easier maintenance on my end - especially since I've had hard times in the past with conditional formatting and adding new rows/columns to my range.

Attached is a sample workbook that I'm hoping I've desensitized enough. If you guys aren't able to replicate the slow churn, then I'll know it's my machine. Otherwise, please feel free to tear apart my code

It is definitely slow here! I think this will be faster though there will still be a lag

Please Login or Register  to view this content.

Not a long lag time at all, thanks! Any insight as to what may have been bogging down the previous code?

Formatting one row at a time is slower than formatting blocks of ranges at once.