Conditional formatting two columns at the same time.

Question

In Sheet2, Column B and Column C, I have text strings in almost every row, starting in Row 2.  I named the range B2:C “Matched”.  The data from every row/record belong together, so B3:C3 belong together, B15:C15 belong together, and B55:C55 belong together. 

I want to set up conditional formatting so whenever a combination from any row in Sheet2, is typed into any row in Sheet1, Column D and Column F, the match will change color in those columns of Sheet 1; Column E is blank.

Below is the formula I put in Sheet1, D2 in order to make the conditional formatting work and I applied it to the range =D2:F45. 

=COUNTIF(Matched,D2:F2)>0

The problem I have with this formula is that it does not recognize the rows in Sheet1, Column D and Column F as belonging together.  As a result Column D can change color by itself while Column F remains unchanged.  I only want Column D and Column F to change color when both Columns match a record in Sheet2 Column B:C, not just one of them.  Can you help?

Answer 1

I have not been successful in getting the following formula to work:

=COUNTIF(Matched,D2:F2)>0

Try the following method.

Define a name "Matched"  and copy the following formula and paste into the "Refers to" field. (Concatenation of the ranges of 2 columns)

=Sheet2!$B$1:$B$100&Sheet2!$C$1:$C$100

Edit the ranges to be concatenated to incorporate sufficient rows of the 2 columns of data to search. However, do not try to contatenate the entire columns otherwise Excel takes too long to perform the required calculations when the Conditional format is applied.

Set the Condional format on On Sheet1 as follows:

Remove any Conditional format currently applied to the required range.

Select Range D2:D45 and hold the Ctrl and Select F2:F45. (so that you have 2 separate ranges selected.)

Open Conditional Format and use the following formula:

=AND(NOT($D2&$F2=""),NOT(ISERROR(VLOOKUP($D2&$F2,Matched,1,FALSE))))

Have tested this and appears to work fine provided that Named Concatenated range is not too large. However, the calculation time does not become a problem if in lieu of naming the concatenated range, you concatenate the 2 columns for the lookup into a speparate column and use that as the Table_Array in Vlookup. You could hide the column.