Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting Comparison Criterion -- cell above Fred Holmes Excel Discussion (Misc queries) 5 February 28th 10 01:35 PM
Conditional Formatting - comparison with another cell Jezza Excel Worksheet Functions 4 October 27th 09 06:27 PM
Conditional Formatting across many cells with a mobile comparison JLatham Excel Discussion (Misc queries) 1 September 27th 06 06:37 AM
Excel Conditional Formatting - Two Dates Comparison [email protected] Excel Discussion (Misc queries) 2 August 21st 06 08:40 PM
String Comparison & Conditional Formatting Blobbies Excel Discussion (Misc queries) 3 April 18th 06 07:43 AM


All times are GMT +1. The time now is 06:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"