How can I conditionally format a cell, base on the contents of a different cell

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.

By: Nick

Leave a Reply

Your email address will not be published. Required fields are marked *