Hightlight based on duplicates AND a certain value

[Salz15]

Hi, I have a spreadsheet which has duplicated values in column B.  I want excel to look at the duplicates in column b and then for rows which have the same, duplicated entry in column B. I want it to ALSO look for a specific number in coumn F.  If excel finds the specified number in column F I want it to highlight both rows with the duplicated figure in column B.  So this would be an example: column B has the same value, its also looking for the number 1154798451 (in column F) and it finds that so highlights both rows that have the duplicated value in column B, anyone know if such a thing is possible?

[#2]

@Salz15 

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.

[#3]

@Hans VogelaarThis 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.

[#4]

@Salz15 

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