Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional Formatting Comparison Criterion -- cell above | Excel Discussion (Misc queries) | |||
Conditional Formatting - comparison with another cell | Excel Worksheet Functions | |||
Conditional Formatting across many cells with a mobile comparison | Excel Discussion (Misc queries) | |||
Excel Conditional Formatting - Two Dates Comparison | Excel Discussion (Misc queries) | |||
String Comparison & Conditional Formatting | Excel Discussion (Misc queries) |