0

Also posted here:

https://www.tek-tips.com/viewthread.cfm?qid=1787250

I have to send workbooks off to managers in different departments and in each workbook, the managers might amend/change certain fields. The fields are not necessarily the same all the time, for example:

Manager 1 - Changes 3 fields in column A, 2 fields in column M and nothing on the rest of the sheet

Manager 2 - Changes 1 field in columns C - F, 5 fields in column J and 7 fields in column Y

Manager 3 - Changes 2 fields in column A, 4 fields in column B and nothing on the rest of the sheet

So I have a macro I am using in another workbook that will compare an 'original' version of a workbook with an 'updated' version but it only looks for differences in one specific column - so perhaps I could modify that macro to accomplish this task.

Here is the link to the macro I am referring to:

VBA - Copy Cells from Column A and B and Paste in New Sheet

I was however wondering, is there no kind of conditional formatting rule that can be applied to the workbooks - BEFORE they are sent to the managers - that highlights a row when a value in that row has been changed and changes the colour of the text in that cell? If there is even something like that which exists...

Eitel Dagnin
  • 959
  • 4
  • 24
  • 61
  • If the editors aren't adding/removing rows or columns then you could use a hidden copy of the original sheet and conditional formatting to compare the cell content between the two sheets. – Tim Williams May 30 '18 at 22:34
  • 1
    Yes, you can use the target and event change features to trigger a formatting. I would personally not highlight and have the macro build a table on a hidden sheet that just lists all cells that have been changed. But, that’s just preference. Changing colors may incentivize some unexpected behaviors. Regardless, I’m positive this is a duplicate question to an extent. Have you done any research? – urdearboy May 30 '18 at 22:37
  • Why isn't Track Changes an option? –  May 30 '18 at 23:08
  • @TimWilliams thank you for your reply, I actually received that exact response from a user on the other forum. Posted the answer below. :) – Eitel Dagnin May 31 '18 at 08:34
  • @Jeeped I am not familiar with 'Track Changes'. I will look into it though. For now, the answer I found to be working is below. :) – Eitel Dagnin May 31 '18 at 08:35
  • @urdearboy Yes I have done research and yes I agree to a certain extent this could be seen as a duplicate question, but so can pretty much any question that is asked. However, my lack in knowledge on the subject lead me to asking the question for guidance on how to solve my issue. Regardless, thank you for your reply. – Eitel Dagnin May 31 '18 at 08:41

1 Answers1

0

Answer received here and working perfectly:

https://www.tek-tips.com/viewthread.cfm?qid=1787250

COPY the SHEET and name it Review, for instance.

Then HIDE the original sheet.

Given that the original (hidden) sheet name is Original, in the Review sheet:

Select the Used Range. The TOP LEFT cell in my selection is A2.

Open the CF Wizard

Use a formula to determine which cell to format

=A2<>Original!A2

...and select a FORMAT.

Eitel Dagnin
  • 959
  • 4
  • 24
  • 61