Explanation

The key to this approach is the calendar header (row 4), which is just a series of valid dates, formatted with the custom number format “d”. With a hardcoded date in D4, you can use =D4+1 to populate the calendar. This allows you to set up a conditional formatting rule that compares the date in row 4 with the dates in columns B and C.

To shade days that are weekends, we are using a formula based on the weekday function . By default, the weekday function returns a number between 1 and 7 that corresponds to days of the week, where Sunday is 1 and Saturday is 7. However, by adding the optional second argument called “return type” with a value of 2, the numbering scheme changes so that Monday is 1 and Saturday and Sunday are 6 and 7, respectively.

As a result, to return TRUE for dates that are either Saturday or Sunday, we only need to test for numbers greater than 5. The conditional formatting formula applied to the calendar area (starting with D4) looks like this:

=WEEKDAY(D$4,2)>5

The reference to D4 is mixed , with the row locked so that the formula continues to evaluate the dates in the header for all rows in the calendar grid.

Explanation

This formula uses 4 named ranges, defined as follows:

width=K6
height=K7
widths=B6:B11
heights=C5:H5

Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, which is cell B5 in this case. To highlight the matching row, we use this logical expression:

$B5=LOOKUP(width,widths)

The reference to B5 is mixed , with the column locked and row unlocked, so that only values in column B (widths) are compared to the value in K6 (width). In the example shown, this logical expression will return TRUE for every cell in a row where the width is 200, based on an approximate match of the value in K6 (width, 275) against all values in K6:B11 (widths). This is done with the LOOKUP function:

LOOKUP(width,widths)

Like the MATCH function, LOOKUP will run through sorted values until a greater value is found, then “step back” to the previous value, which is 200 in this case.

To highlight the matching column, we use this logical expression:

B$5=LOOKUP(height,heights)

The reference to B5 is mixed, with the column relative and row absolute , so that only values in row 5 (heights) are compared to the value in K7 (height). In the example shown, this logical expression will return TRUE for every cell in a row where the height is 300, based on an approximate match of the value in K7 (height, 325) against all values in C5:H5 (heights). This is done with the LOOKUP function:

LOOKUP(height,heights)

As above, LOOKUP will run through sorted values until a greater value is found, then “step back” to the previous value, which is 300 in this case.

Highlight intersection only

To highlight the intersection only, just replace the OR function with the AND function:

=AND($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights))