This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change criteria by editing row 3 and 4.

Conditional formatting formula:

How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

1. Select cells B7:E28
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7))))) in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighted cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

How the conditional formatting formula works in cell C8

Step 1 - Understand relative and absolute cell referencing

Formula in cell B7:

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7))))

The formula in cell C8 changes to:

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B8)*($B$4>=$B8))*(IF(ISBLANK($C$3), 1, $C$3=$C8))*(IF(ISBLANK($D$3), 1, $D$3=$D8))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E8)*($E$4>=$E8))))

Step 2 - First criterion, dates

I have bolded the first date criteria below:

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B8)*($B$4>=$B8))*(IF(ISBLANK($C$3), 1, $C$3=$C8))*(IF(ISBLANK($D$3), 1, $D$3=$D8))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E8)*($E$4>=$E8))))

IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B8)*($B$4>=$B8))

becomes

IF(AND(ISBLANK({40554;40568])), 1, (40554<=40574)*(40568>=40574))

becomes

IF(AND({FALSE;FALSE}), 1, (TRUE)*(FALSE))

becomes

IF(FALSE), 1, (TRUE)*(FALSE))

becomes

IF(FALSE), 1,0) returns 0.

Step 3 - Second criterion, color

I have bolded the second criterion below:

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B8)*($B$4>=$B8))*(IF(ISBLANK($C$3), 1, $C$3=$C8))*(IF(ISBLANK($D$3), 1, $D$3=$D8))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E8)*($E$4>=$E8))))

IF(ISBLANK($C$3), 1, $C$3=$C8)

becomes

IF(ISBLANK("blue"), 1, "blue"="orange")

becomes

IF(FALSE, 1, False) returns FALSE.

Step 4 - Third criterion, products

I have bolded the third criterion below:

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B8)*($B$4>=$B8))*(IF(ISBLANK($C$3), 1, $C$3=$C8))*(IF(ISBLANK($D$3), 1, $D$3=$D8))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E8)*($E$4>=$E8))))

IF(ISBLANK($D$3), 1, $D$3=$D8)

becomes

IF(ISBLANK("Product 4"), 1, "Product 4"="Product 3")

becomes

IF(FALSE, 1, FALSE) returns FALSE.

Step 5 - Fourth criterion, price

I have bolded the fourth criteria below:

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B8)*($B$4>=$B8))*(IF(ISBLANK($C$3), 1, $C$3=$C8))*(IF(ISBLANK($D$3), 1, $D$3=$D8))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E8)*($E$4>=$E8))))

IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E8)*($E$4>=$E8))

becomes

IF(AND(ISBLANK({15;25})), 1, (15<=67,8)*(25>=67,8))

becomes

IF(AND({FALSE;FALSE}), 1, (TRUE)*(FALSE))

becomes

IF(FALSE, 1, 0) returns 0.

Step 6 - All criteria together

=SUMPRODUCT(IF(AND(ISBLANK($B$3:$B$4)), 1, ($B$3<=$B7)*($B$4>=$B7))*(IF(ISBLANK($C$3), 1, $C$3=$C7))*(IF(ISBLANK($D$3), 1, $D$3=$D7))*(IF(AND(ISBLANK($E$3:$E$4)), 1, ($E$3<=$E7)*($E$4>=$E7))))

becomes

=SUMPRODUCT(0*FALSE*FALSE*0)

becomes

=SUMPRODUCT(0) returns 0. Row 8 is not highlighted.

Final notes

The formula also works if you only have one criterion or two criteria. Try it yourself.

Download excel example file

quickly highlight records in a list AND logic.xlsx
(Excel 2007 Workbook *.xlsx)

Functions:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

Related posts:

Quickly highlight records containing text strings in excel (AND Logic)

Quickly highlight records in a list using multiple criteria in excel

Compare two lists of data: Highlight records existing in only one list in excel

Merge two columns with possible blank cells in excel (formula)

Highlight overlapping date ranges using conditional formatting in excel