highlight a cell based on 2 cells matching

[schroederd1]

I'm new to the conditional formatting so this may be really simple but I don't know where to start. I would like to compare columns A & C in workbook1 to A & C in workbook2. Columns A maynot be sorted the same in each workbook. First I think it would have to find the value in A1 of workbook1 in column A of workbook 2. If it finds the match then can it hightlight the value in C1 of workbook 2 if C1 is not a match. Example:

Workbook1 Workbook2
A B C A B C
1 Bob 2 1 Bill 4
2 Bill 4 2 Pat 7
3 Pat 6 3 Bob 2
4 Tim 8 4 Tim 2
In this example it would highlight C2 & C4 in Workbook2 since these values don't match to Pat and Tim's Column C values in Workbook1.

Thanks so much!!!!!

[monarchd]

In Workbook2, in Cell D1, I used this formula which showed "Matched" if it matched on A1 and then on C1.



=IF(A1=[Workbook1.xls]Sheet1!$A$1,IF(C1=[Workbook1.xls]Sheet1!$C$1,"Matched", "Not Matched"),"Not Matched")


See if that formula gets the results you want.

[monarchd]

If that works, you can apply the conditional formatting to the rows where "Not Matched":

In Workbook 2, go to Conditional Formatting, choose "Use formula to determine..." and enter this formula:



=$D1="Not Matched"


Click Format... to choose a Fill color or however you want to format that row.

Click OK.

Now let's apply that to the whole area, so we can format the entire row. In the "Applies to" put in the range, in this example it's:



=$A$1:$D$4


That should do it. You can hide column D if you don't want to show your "helper" column.