Comparison of columns using Conditional Formatting

  • Thread starter Thread starter Andrea
  • Start date Start date
A

Andrea

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 necessaryConditional Formatting formulas for these 5 comparison scenarios?

A B
1 data1 data1

2 (blank) data1

3 data1 data2

4 (blank) DATE>7daysold

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 revisionor 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!
 
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.
 
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.
Click to expand...

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
 
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
Click to expand...

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.)
 
Back
Top