Conditional Formatting causing scrolling lag

[Glasgowsmile]

Good day.

I have this formula for conditional formatting:

=AND(AN$6=1,$AN$8:$BV$374<>"",$AN$7:$BV$374<$BX7)

It strikes through the text if the conditionals are met... but the scrolling of this sheet takes a nose dive the moment I use it. How can I correct this? Should it be done in VBA instead?

 

[RoryA]

You're comparing over 25000 values for every cell, which is going to take time. It would be more efficient to at least put the $AN$8:$BV$374<>"" calculation into a cell somewhere and refer to that directly, since it doesn't change for each cell.

 

[Glasgowsmile]

You're comparing over 25000 values for every cell, which is going to take time. It would be more efficient to at least put the $AN$8:$BV$374<>"" calculation into a cell somewhere and refer to that directly, since it doesn't change for each cell.

Thanks,

I'm trying this instead which is working much better but any suggestions for improvement?

=AND(AN$6=1,AN7<$BX7)

Applying to $AN$7:$BV$374

 

[RoryA]

You might try:

=IF(AN$6=1,AN7<$BX7,FALSE)

though I'm not sure it will make a lot of difference.