Conditional formatting expert needed! Comparing 2 arrays.

Hi all,

I apologize for the vague title. but I could not think of a way to properly explain the problem in a single line with so few characters... So I was hoping to pull in the conditional formatting experts who can tell me if it is even possible to accomplish what I am trying to do here!

I am using Microsoft Office Professional Plus 2010.

I have two arrays that look like this:


The column A shows the "ID" of a task.
Column B shows the completion date of the task

I want to be able to automatically highlight the "Actual" array IDs and dates, according to if the "Actual" is matching ( or greater/less than) the "Proposal". Also if there is no matching task ID in proposal.

- Red if there is no matching task ID in the proposal array
- Orange if the Actual date is later than the proposal date
- Dark green if the Actual date is the same as the proposal date
- Light green if the Actual date is earlier than the proposal date.

Is this possible? I am struggling with how to get conditional formating to search an array, match the ID, then apply the formatting based on a different cell in the same row of the matching ID.

Any help here would be greatly appreciated!

To help visualise the needed automatic formatting:


I will also edit this post in case I managed to resolve it myself. Thanks for your time.