Trying to use conditional formatting to show if any text cell has been changed

**abbelougee** · 01-20-2011, 07:41 PM

Yet Another Conditional Formatting Question...
I'm using an Excel spreadsheet to track agenda items for an upcoming seminar our department is facilitating. The agenda items and other associated information such as date, time, presenter name are being entered by the staff member responsible for each particular item, then I add the new information to the formal agenda document.
Because all of the information is subject to change up until a day or so of the event, I need to find changes easily. I have a column for "date updated" and formatted it to be red anytime the date is within the past week (including today). This works fine in theory -- well, in practice too -- but it only works if the staff member making the changes remembers to put in an updated date. Which they often do not.
I can't figure out how create conditional formatting that will turn each updated cell red if any change is made to it and maybe also to add the date in the 'updated date' column (which will then turn red as above). All of the entries are text, all are different (think session name, presenter, meals and/or lodging required), and some may be changed several times and others not at all.

Thanks in advance for your help -- an excel guru I am not!

**werfnort** · 01-24-2011, 05:12 PM

Here's an interesting potential solution. Let's say you have three columns of information, A, B, C.

I would suggest creating a new column in D that concatenates the first three columns. So =concatenate(a2,b2,c2) - then drag that down for the entire column.

Now copy Column D, paste special as values into Column E.

Then set up conditional formating for the first three columns like this - "=IF($D2<>$E2,1,0)"

Hide columns D and E. As people make changes, Column D will continue to update because the formulas are intact. Column E will stay at whatever you set it at the last time you made your changes.

Every time you go in and and update your info, copy Column D and Paste special as values over Column E. That will wipe all of the flagged cells, and basically start fresh until you can look at it again and make changes.