[VBA] Highlight Differences Between Two Worksheets with Old Worksheet Data Crossed Out

jindon!!! I'm so glad I found you again, I hope you are well! Your code was amazing and really helped me out the past couple weeks.

I'm sorry, but I don't really understand what you mean by "Replace "test" sub with the following code, keeping "mySort" sub intact."
I pasted and ran your code and it gave me a Compile error: Sub or Function not defined and it highlighted the mySort.

Sorry I'm not very literate in VBA, you're going to have to really dumb down your responses to me. Have a great day everyone!

You have "test" sub procedure and "mysort" sub procedure in Module10.

Just replace "test" sub procedure with the code I posted.

Yeah, I was able to make it work ONCE, but I still have no idea how I got the code to work, nor can I use it on any other files.

I'm not understanding what's going on, apologies.

Good Morning/Afternoon/Evening jindon.

If perhaps you could start from a completely unedited file maybe I would understand better. Please find below an Excel sheet that has the two sheets renamed accordingly with no macro attached.

Can you please teach me how to apply this code to a file like this one.

Thanks,

Shutdown pc already so I will look at it tomorrow.

No problem, have yourself a wonderful night.

Code is working just fine... not only the first time.

If you are changing sheet names for "2-19-2021" & "5-27-2021", sheet names must be changed accordingly.
This code gets OLD data from most left worksheet and New data from 2nd worksheet, so you don't need to change the sheet names.
New worksheet will be created in 3rd worksheet.

Please Login or Register  to view this content.

Good Morning jindon,

Apologies, I've been going back and forth between my remote work computer and my main PC which does not have Excel, so I must have not been copying it correctly in its entirety. Your code as always is perfect my friend, thank you very much for your time and assistance. I wish you health and good fortune, be well brother.

Forever Grateful,
NotSoTechSavvy

You are welcome and same to you.

I don't understand why you are not using the code I last posted in #10.
1)
change one line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) Border? where? if you are talking about grid lines, they are always there, but it is not recognizable when cell is colored.

OK, 2 more changes in "test" sub in bold.

Please Login or Register  to view this content.

I get a Compile Error:
Sub or Function not defined

Then it highlights the mySort

You should not delete mySort sub procedure...

Apologies, I don't understand what you mean. I did not delete anything, I just copied and pasted the code into the Module and ran it. It came back with the Compile Error.

Can you test it on the following Workbook to see if there is an issue?

Thanks,

It is consists of 2 sub procedures.

test subprocedure and my sort subprocedure and both MUST be kept.

Please Login or Register  to view this content.

That worked perfectly for solving the first issue, you are absolutely amazing. Can you also have ALL BORDERS for all cells that way they show up in the PDF printout around the highlighted cells as well?

Upload a workbook with EXACT format to avoid misunderstandings.

Please see attached Sheet3 which has borders around all cells.

Thanks,

add one line in bold.

Please Login or Register  to view this content.

Thank you again!! This worked perfectly for my equipment schedules, exactly as needed.

I have another issue that perhaps you'd be so kind to assist in. We also run electrical/plumbing schedules and I've tried to apply your code to this with limited success.
The first issue is similar to the previous project as numbers are being saved as text and they're justified differently. The second issue is much more difficult, as it requires sorting with some sort of data recognition.

Issue #1: Numbers Saved as Text & Justified Text:
Please see Sheet1 for CURRENT output
Currently, the schedule has the same issue where numbers are being highlighted despite no change due to the numbers being saved as text.
See Sheet1 Cells F7, F11, F16, F20.
Also, the cells are justified right for some of the information where it should be justified right for all except ROW 1
Please see Sheet2 for CORRECT format.


Issue #2: Row Sorting and Row Addition/Deletion
Please see Sheet1 for CURRENT output
Item #B5 has two connections in the first sheet (2021-06-18) and then three electrical connections in the second sheet (2021-07-06).
When the excel sheet is created, any line item that has multiple connections are shown in the row below with a "-" underneath in Column A with the information in Columns F-O.
Because we previously used Column A to sort, the extra utility information goes to the top.
Is there a way for us to sort so that utilities for B5 and B6 show up under the correct rows (See Rows 26-28 on the Sheet2).
Also, it needs to pick up the additional utility row (Row 28 was an added line from the 2021-06-08 sheet) and highlight them or the deletion of a row of utilities.
Please see Sheet2 for CORRECT format.

One thing very clear is that it is impossible to compare multiple same value against multiple same value.

You have multiple "-" in col.A.

I assumed the would be the case.
Thank you so much again for all your help! Have a wonderful day!

Yep, that make sense.

I will be very busy today, so next my response will be late this afternoon.

No problem, I'm just glad that I stumbled upon a possible work-around. Have a great day!

This one will create copy of 1st and 2nd worksheet to 3rd & 4th, so will not touch original sheets and newly added 5th worksheet will give you report.
And convert text number to real number in both copied worksheets.

Please Login or Register  to view this content.

Good Morning/Afternoon/Evening jindon,

Thank you very much for the revised code, it seems to have worked with some slight issues still, please see attached Excel file.

This is the rough template of the information I'm trying to process all at once. The issues I have noticed are the following:
1. The ROWS 231-253 are being placed at the bottom instead of after their respective Item#s.
2. Instead of a "." after the Item#, can we perhaps use a SPACE (Example: 1, 1.1, 1.2, 1.2 A, 2, 3, 3 A, etc...)
3. All rows EXCEPT Row 1 are not all Justified LEFT, the information is a little difficult to read at times

Thanks,

Good Evening jindon,

One slight oversight on my part, it seems excel formatting uses the SPACE A in certain instances to denote time. Please see Cells A14 & A17 of Sheet Date 2(2) for this issue. If you look on Sheet3, Rows 3 & 4 are in the incorrect row and with the incorrect Item# because of this.

Thanks,

Yeah, that was a side effect of changing 3.A to 3 A, Excel read it as a time.

I have edited one line in the last code in bold.

Yep, that worked amazingly as always. Thank you so very much for taking the time to help me out with this project, I will stress test the macro to see if there are any things that I personally missed that might need some reworking.

Have yourself a wonderful night and once again thank you so much.

Good Evening jindon,

Apologies, I just found one slight thing that needs fixing. It seems that when a row has DELETED in column B, it does not cross out the information for the Columns G-AB. Please see Sheet3 Rows 18, 23 & 24, in Columns G-AB those should have a strikethrough.
Please see Sheet4 Rows 18, 23 & 24 for the correct outputs.

Thanks,

Insert one line in bold.

Please Login or Register  to view this content.

Thank you again, have a good night!

Good Morning/Afternoon/Evening jindon,

I have some comments that I need your assistance with to revise to macro.

Previously, we had dashes "-" in empty cells, but I've now removed them in my original input files. When I run the code now, the output has the following issues.
a. The revised are going to the top as they don't have an Item# in front because they're no longer adding A-Z to the Item# in column A
b. The text is spilling into the adjacent empty cells which is cluttering the chart

Is there a way to have the cells that previously were empty with a placeholder "-" be completely empty without the data overlowing and having the previous formatting?

Once I solve this, there are some other slight issues but I cannot put out an example file until this issue is resolved.

Thanks,

I feel like you don't have your own vision of what you want to have as a Final result, so think about if carefully and when you find it for yourself, upload a workbook as a FINAL format.

Good Morning/Afternoon/Evening jindon,

I have done a bunch of testing/review of the template to finalize this project. Please find attached the template how I'd like it to look.
Sheet 4 has the correct data input, I only made changes to Rows 1-103 to use as examples.

Note the following changes:
1. For all data in Columns D-X:
a. If there is no data in the cell of the Date1 sheet, do not show the old data, just show the new data in Sheet3 (See Sheet4 Rows 13-24) We are removing the extra "-" within each cell if uncessary
b. If there is no data in the cell of the Date2 sheet, do not show the new data, just show the old data crossed out in Sheet3 (See Sheet4 Rows 13-24) We are removing the extra "-" within each cell if unecessary
c. If there is data in the cells for Date1 & Date2, show the old data crossed out and the new data after a space (See Sheet4 cells F13, E33, F35)

2. Currently Column X is not printing old/new data correctly. See Sheet4 Rows 39, 68, 92, 102)
It is only printing the current data from Date2, please have it show the information according to the parameters above.

3. I would like to LOCK Column A and Rows 1&2 (See Sheet4)


Again, thank you very much for your time and for sticking with me through this project.

Have a great day!

Replace "test" sub procedure with the following and see if it works as you expected.
mySort sub procedure must be kept intact.

Please Login or Register  to view this content.

Good Afternoon jindon,

Thank you very much for your assistance again.
I've uploaded the resulting file, it seems as though the results for statement 1a are incorrect, it is crossing out the data from Date2 instead of just highlighting them.
Please see Sheet3, for example cells G16,J16, K16, L16, G17, J17, K17, L17 should not be crossed out.
1a. If there is no data in the cell of the Date1 sheet, do not show the old data, just show the new data in Sheet3 highlighted.

Also, the ROWS 1&2 are locked, but COLUMN A is not locked. Can you have both Rows 1&2 AND Column A locked. That way when I pan right I can still see the Item#'s.

Thank you very much =]

OK,
try change
1)

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Good Afternoon jindon,

Thank you very much!!! It's perfect!
I will be testing it out the next couple of weeks to see if there is anything wrong, but after my preliminary check of the results it is functioning perfectly. If there is anything I catch, I will make sure to compile all the information/errors to minimize any extra revisions if necessary, but I don't believe there will be any need.
Once again you have outdone yourself and I thank you so much for your time and patience. Have yourself a wonderful day.

Thanks,

Good Morning/Afternoon/Evening jindon,

Okay so I've tested both excel files extensively and they work perfectly, again thank you very much for your work!!!!
I have one final request if you would. I need to consolidate these two excel workbooks into one, the final product should look like the Final Template file.

The MEP Excel Template will be the starting point upon which the rest of the macro will be added.
First, I need to add another sheet (Sheet4) which will contain all the information in the following Columns (A,B,C,D,E,F) noting and highlighting all the changes in those columns as well.
However, at the top of Sheet4 it should say "EQUIPMENT SCHEDULE" as shown in the Final Template (It should not say MEP SCHEDULE on the top of Sheet4 (this may require manually changing the name).
Then I need the page layout ONLY for Sheet4 to be changed according to the HeaderandFooter macro that I created (apologies it's not very elegant as it was done using the Record Macro tool)


Thank you very much for all your assistance, have a wonderful day!