CONDITIONAL FORMATTING ROWS with VLOOKUP RANGE

Question

Hello,

I have 26 customers listed in a separate sheet of an Excel file, labled 'ATP Customers' .  I would like to highlight rows of data they are listed in on another sheet in the same Excel File, labled 'Current'.  The row formatting depends on a match from the CUSTOMER NAME from 'ATP Customers' in column A to a CUstomer column (AA) in the 'Current' sheet and also if there is an "ATP" code in column AE of 'Current'.

How can I highlight rows of data using a VLOOKUP/ AND statement?

For example: if AA:AA [Current] = A:A [ATP Customers] and AE:AE="ATP" then highlight that row in yellow.

Please help,

Thanks in advance.

Answer 1

I'm sorry, what I meant is how can I highlight rows, based off of just the customer list on the Customer sheet.  No other conditions apply.

Answer 2

Similar as above, but with

=AND(ISNUMBER(MATCH($AA2,'ATP Customers!$A:$A,0)),$AE2<>"ATP")

and another highlight color.

Answer 3

Thank You!

Can you also help me figure out how to format the rows (starting in row 2) with matching only the list of Customers from the ATP Customers tab, without the 'ATP' condition?

Answer 4

Select the entire range that you want to format conditionally. I will assume that it starts in row 2.

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(ISNUMBER(MATCH($AA2,'ATP Customers!$A:$A,0)),$AE2="ATP")

Click Format...

Activate the Fill tab.

Select yellow as fill color.

Click OK, then click OK again.