Forum Discussion
Conditional Formatting issue
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.
Try the following:
Select B6:F91.
B6 should be the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > Manage Rules...
Delete all existing rules.
Click New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=OR(B6=$B$2:$F$2)
Click Format...
Activate the Font tab.
Select Bold, and Red as font color.
Click OK until all dialogs have closed.Enter the following formula in G6, then fill down:
=SUM(COUNTIF(B6:F6, $B$2:$F$2))
SOLVED
Beautifully done !
Your instructions on creating the Conditional Format and counting results were simple and very clear. And they worked perfectly. I am very grateful for your help.
I was unaware of the OR function and the importance of the sequence of the parameters in the comparison test. I also missed the fact that I could use a range in the criteria portion of the COUNTIF function.
Thank you again.
