Conditional formatting oddity?

Question

I'm running Excel 2021 on Windows 10 Pro (64-bit).

I seem to be having some form of delay with the application of Conditional Formatting,

where I have to scroll past the cells subject to the formatting, before the effect takes place.

Sheet2 looks like:

I want the conditional formatting to affect any cell in $B$6:$F$91 that matches 

any of the numbers entered in $B$2:$F$2 by changing the font color to Red and Bold.

The rules manager shows the following:

If I enter a 4 in $D$2 and press enter, cell $D$6 does not change until I click in the window

outside the sheet, or scroll down so that row 6 disappears at the top of the screen.

Similarly, if I clear the contents of $C$2, the number 03 in row 6 stays red until I scroll

row 6 off the top of the screen.

I don't know if I've missed some important aspect of conditional formatting or 

if I've managed to affect some global setting in Excel.

Once I've figured out this problem, I want to display a count of the cells in each row 

that were conditionally formatted, a 2 for example in $G$6.

Any help would be appreciated.

***Moved from Windows / Windows 10 / Settings***

Answer 1

=ISNUMBER(MATCH(B6,$B$2:$F$2,0))

For G6:

=SUM(--(ISNUMBER(MATCH(B6:F6,$B$2:$F$2,0))))

which may need to be array-entered with Ctrl+Shift+Enter depending on your version of Excel.

Regarding the delayed showing of the formatting, it may be you're running short of memory? Maybe the streamlined conditional formatting may be enough to lose that.

Answer 2

Hi GeorgeSinkinson,

Thanks for visiting Microsoft Community.

From your description, it seems that the calculation of conditional formatting experiences significant delays after the cell values are changed.

The conditional formatting rule you provided in the screenshot, which bolds and changes the font color to red when the values in $B$6:$F$91 match any value in $B$2:$F$2, can be optimized as follows to achieve the same effect:

Set five different conditional formatting rules corresponding to five different data ranges (B6:B91, C6:C91, ... ). This way, each cell will have independent rules, and they will not recalculate due to updates in other values.

=OR(B6=$B$2,B6=$C$2,B6=$D$2,B6=$E$2,B6=$F$2)

This formula can be applied to five different ranges, and theoretically, they will automatically adjust the parameters in the formula as shown in the image below:

Does this improve the situation? Please feel free to share the results with me. Additionally, if it does not contain sensitive information, you can directly share the file with me, and I will conduct further testing.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist