Conditional Formatting using other cell value

Copper Contributor

Hi, I am struggling to apply the conditional formatting based on other cell values. Kindly help.

The case is, in one column I have qtrs. like Q-1, Q-2, Q-3 Q-4 and in other column there are branch codes and these branch codes are repetitive based on some activities. I want to highlight only those cells of branch codes column which are repetitive in that qtr. only.

Suppose if I filter out Q-1 it should show only those cells which appear more than once in Q-1 only and likewise in other qtrs.

2 Replies


Let's say the quarters are in Q2:Q100 and the branch codes in B2:B100.

Select B2:B100. B2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula


=COUNTIFS($B$2:$B$100, $B2, $Q$2:$Q$100, $Q2)>1


Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

@Hans Vogelaar thanks a lot sir, it's worked. I have tried lot of formulas and conditions but never thought it is so simple. 



Lalit Kumar Pandey