Conditional Formatting slowing down worksheet

[JKW]

I have quite a large spreadsheet which is run daily to show stock levels in a traffic lights style format, the issue I'm having is that as the sheet has grown the formatting seems to be massively slowing everything down to the point where it is taking twice as long as it needs to because of the pause between moving from one cell to the next.

After doing a bit of research I can see that this is because of the volatility of the conditional formatting but I'm not sure how to get around it. Can anyone suggest an alternative to what I currently have set up? (below)

 

[Joe4]

Welcome to the Board!

Applying it on 100 rows from columns L to Q shouldn't be that much to affect it greatly.
Are you sure it is the Conditional Formatting slowing things down? If you remove it, does the sheet run much faster?

I suspect that there may be other things slowing things down, such as:
1. Do you have a lot of formulas on the sheet?
2. Do you have hyperlinks or links to other workbooks or the internet?
3. Do you have event procedure VBA code (VBA code that runs automatically)?

 

[JKW]

Thank you, I think it’s the conditional formatting because I seem to be able to move around the rest of the sheet fine, it’s just when I try to change anything within the L:Q columns that it freezes.
There are a couple of simple formulas elsewhere in the workbook but not loads, one hyperlink to another worksheet and no VBA code

 

[Joe4]

Try making a copy of the sheet, removing the Conditional Formatting, and then then making those same changes, and see if it works fine then,
If it does, then it appears that the Conditional Formatting is contributing to the slowness.

You may be able to use VBA to do this instead of Conditional Formatting, but we would need to know a little bit more about how/when this should work/run.
- Are you wanting to apply this on existing data, or new data as it is manually entered into the sheet? Or both?