Conditional formatting: a resource hog: VBA alternative?

[mskli]

Hi:

I have a spreadsheet that looks at live stock data. I have some conditional formatting that is very useful but it can slow down the sheet at volatile times. The question is - can VBA code be used instead of the conditional formatting to achieve better results? (using less cpu resources)

If so - can someone provide sample code that would update the "background fill" after each calculation of data comes in?

I would want to change the background fill in cells b5-b25 using the corresponding value in cells z5:z25. The value in z5 corresponds to the background in b5, z6 to b6 etc etc. There are three possible values in column Z (1,2,3). When 1 - background=white, when 2- background =grey when 3- background =red.


Can someone provide sample code for this so I can test it next week?

thanks

 

[jasonb75]

I can't see such a simple cf rule for such a small range being a drain on your system resources.

You could possibly optimise the cf rules to improve things very slightly under ideal conditions if you have not already done so, but I think that converting this to vba will be a false economy unless you don't want the formatting to be live. i.e. that you will manually run the code to refresh the formatting rather than it happening automatically.

 

[mskli]

Sorry - I actually have about four hundred of these conditional formats. I would simply apply the code to all of them afterwards. It has to happen in real time. The spreadsheet will calculate about once a second.

I have 64 gb of ram and I'm using a 64 bit excel.

 

[mskli]

Here is a small screenshot (only about half of the spreadsheet)

 

[Fluff]

If you want VBA to run every second applying ~400 different formats to cells, it will almost certainly be worse than CF.
You'd be lucky if it finished the first set of formats, before it had to start again.

 

[mskli]

ok. thank you

 

[Fluff]

You're welcome.