Forum Discussion
Hightlight based on duplicates AND a certain value
Select the range in columns A to F that you want to format.
I will assume that the active cell in the selection is in row 1.
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
=AND(COUNTIF($B$1:$B$100,$B1)>1,COUNTIFS($B$1:$B$100,$B1,$F$1:$F$100,1154798451)>0)
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
HansVogelaarThis is great thanks, I was almost there with it but not quite. Unfortunately the file has 328000 rows and and this this is too much for it. It crashes when I try and put the formular in. Thanks for your help anyway.
If you're willing to use a helper column, that should avoid crashing.
Enter the formula in a free cell in row 1. I'll use G1 as example, but any other column is fine too.
=AND(COUNTIF($B$1:$B$32800,$B1)>1,COUNTIFS($B$1:$B$32800,$B1,$F$1:$F$32800,1154798451)>0)Change the ranges if necessary, then fill down to row 32800 (or to the end of the data).
Create a conditional formatting rule as in my previous reply, but with the formula
=$G1
