I need excel sheet that will highlight cells that contain values not found in a certain list with red color on condition that their adjacent cells in the the next column contain certain values. Kindly find attached an excel sheet demonstrating what I want.
https://drive.google.com/open?id=0B2QP7WmmT3K5YW9LTzRsQm9SSUk3QVpMTkJQRHUtLVJHeERR
Copy the entire list of 5,025 "Doctor Name" from the "Data" sheet to Column "M" in the "Template" sheet. Copy the "Product Name/Activity" list from the "Data" sheet to Column "N" in the "Template" sheet. In the Template sheet, highlight cells E1:E17 and type this formula in the conditional formatting box : =COUNTIF($M$2:$M$5025,E2)=0.Then choose the format you desire.
Next, highlight cells F2:F12 and enter this same formula :COUNTIF($M$2:$M$5025,E2)=0.Then format as desired.This will marry the cells in unison with matching formatting.
