Comparison of columns using Conditional Formatting
Hello there!
I'm working on writing some macros to process a tracker comparison, and need to establish conditional formatting based on a comparison (of text or date range) in the preceding cell. Can anyone please assist with the necessary Conditional Formatting formulas for these 5 comparison scenarios? A B 1 data1 data1 2 (blank) data1 3 data1 data2 4 (blank) DATE7daysold 5 (blank) DATE<7daysold Note: On my spreadsheet, 'data1' and 'data2' may be text or dates. They will always be the same type in respective rows. (comparing text to text, or date to date. Not comparing text to date.) 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 Thank you kindly for your assistance! |
Comparison of columns using Conditional Formatting
P.S.
I am using the "Record Macro" feature, so I don't need that written code, just the formula to enter into the "New Rule" dialog box under Conditional Formatting. |
Comparison of columns using Conditional Formatting
P.S.
I am using the "Record Macro" feature, so I don't need that written code, just the formula to enter into the "New Rule" dialog box under Conditional Formatting. If you expect to use your recorded code 'as is' then perhaps you may want to post it so someone can cleanup the recorder junk (if you don't know how to do that yourself)!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
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.) |
All times are GMT +1. The time now is 03:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com