View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf[_3_] MyVeryOwnSelf[_3_] is offline
external usenet poster
 
Posts: 56
Default Comparison of columns using Conditional Formatting

A1 and B1: data matches, nothing has changed. No cell shading
A2 and B2: no match (new data) shade B-cell PINK to indicate
a revision or reforecast
A3 and B3: no match (updated data) shade B-cell PINK to
indicate a revision or reforecast
A4 and B4: shade B-cell GREY to indicate a completion older
than 7 days
A5 and B5: shade B-cell BLUE to indicate a completion within
in the last 7 days


With column B selected, something like these formulas in this order might work.

BLUE fill for
=AND(A1="",ISNUMBER(B1),B1=TODAY()-7)

GRAY fill for
=AND(A1="",ISNUMBER(B1),B1<TODAY()-7)

PINK fill for
=A1<B1

Notice that ISNUMBER is used to detect dates. In Excel, a date is just a number formatted in a special way. Since you wrote "'data1' and 'data2' may be text or dates," non-date numbers are ruled out, making the solution easier.

Also, it wasn't clear what's expected when DATE is exactly 7 days old. Make appropriate adjustments if I guessed wrong.

Hope this helps getting started.

(I have Excel 2010.)