Forum Discussion
Conditional Formatting using other cell value
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.
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.HansVogelaar thanks a lot sir, it's worked. I have tried lot of formulas and conditions but never thought it is so simple.
Regards
Lalit Kumar Pandey
