VBA or Macro to Check if Information has been Added, Removed, or Stayed the same.

[Lowen]

Hello everyone!

Over the last couple of days I have been working on a Workbook for my employer. We have TV's that we use to relay pertinent information to employees. Recently they asked me to put a spreadsheet that shows all of the information that is listed week by week. They also want to know what is being removed and added per week. I was told by another forum member that this would probably be easier to do with a VBA or Macro. The current Workbook has a calendar and then an added and remove page followed by the next month.

I used conditional formatting to make information that stayed the same Green, Information that was added as Yellow, and Information that was removed in red. With help from another forum member we were able to come up with something that looks promising.

On the added removed page we made formula's to take the information and sort it based on if it was added or removed and input the value in the correct cell in the table. I believe it also links to the conditional formatting.

My issue is that the information that is populated in the tables on the added and removed page is all spaced out. I was hoping that it wouldn't have spaces in between the information, I was told if I were to change it, it would not populate the information correctly and would also mess up the conditional formatting.

The second issue I am having is with conditional formatting. I cannot get it to work correctly. I am going to upload a copy of the current workbook that I have.

If anyone has any advice or could help me out with finishing this project off I would greatly appreciate it! This would take a huge weight off of my shoulders to have this thing be pretty much automated.

Here is the sheet I have been working on: Monitor Update Calendar.xlsx

[Lowen]

Here is an updated workbook.

Monitor Update Calendar.xlsx

Currently this is almost everything I want it to do. My only problem is going to be how labor intensive it will be to go from month to month trying to get these formulas to work. It also does not let me line up the tables on the second page neatly. I also do not want the information listed on the second page next to tables, and I wanted to see if I could get the information to populate in the tables with no spaces going from top to bottom. I was told that a VBA would be the only way that I can get this to be seamless and looking professional. Please if anyone can help me out I would appreciate it! I was tasked by my employer to set this up and I'm going to have to demo it soon. If you have any questions about the current workbook feel free to ask!!

Thanks in advance!!

Lowen

[watersev]

hi Lowen, I can not say I understood what was the desired output layout for "Added and Removed" sheet. This is my option for it. Please check attachment. Press ALT+F8, select "test", press Run button. Check current month "Added and Removed" sheet.
Another option is not to have additional sheet for added and removed but use colouring for added (mark yellow) and removed (mark red) right on the July 2015 itself. It requires some code changes.

[Lowen]

Hello Watersev,

Thank you for getting back to me so fast, I really appreciate it!! I ran it the way you said to and there were errors on the page when I went to the formulas tab. Here is a screen shot of what it looked like:

After test run.JPG

I went ahead and cleared all of the formatting on the July 2015, and added and removed July pages and resaved them to upload. Im sorry I was unclear about what I wanted the functionality to be. In the new workbook. I added a key in the new upload that explains what I was hoping to achieve with this macro. I would say that your method of listing it all out in a straight line would be good, but the person who I give this report to likes things to be easy to read and was asking me if I could put it into chart by week. The new documents below will explain. Thank you for all of your help! You have no idea how much easier this will make life for me!!

Monitor Update Calendar Excel Forum.xlsx

Thanks again!

Lowen

P.S. Is there anyway if this gets up and running that a button could be placed at the top somewhere in the July 2015/Note section of the workbook to run this after I input the data for each month? I would just have it say something like "Run Report"

Thank you!

[watersev]

Please check attachment. You should see a new tab on the ribbon - Calendar monitoring. It has a button - Report. Click it and check result

[Lowen]

Hey Watersev,

Everything is looking great!!! I think we almost have this thing knocked out!! that button at the top is exactly what I was looking for! I think at this point its all about getting the right conditional formatting done, and the correct information in the boxes on the added and removed page. Also is there anyway that the sheet could all remain white until you hit the button and then everything populates? And also a capitalized M in Calendar Monitoring in the ribbon. Ill include some screen shots of what I mean. Thank you for all of your help I really do appreciate it!!.

Update Sheet.JPG

[watersev]

1. The code does not use conditional formatting. Colouring is done by the code.
2. The "July Added and Removed" sheet is cleared before outputting result.
3. Letter "M" is capitalized in the ribbon.

[Lowen]

Watersev,

Im sorry, that was my fault. Everything was showing up yellow because there were conditional formats still in the workbook. I went into conditional formatting and deleted all of them, and now it looks great!! I guess my only question is, when I go into August am I going to have to change any of the code in the VBA to reflect the changes in the dates/tables? Thank you so much!!! You have no idea how much this is going to help me!! You are a lifesaver!!!

Lowen

[Lowen]

Watersev,

I just noticed something that I wanted to ask you. The information in cells A3-A5 on the "July added and Removed" page, should go under E3-E5 because it wasn't added until 7/6/15. Likewise the information in E3-E5 should go under A10-A12 because it wasn't added until 7/9/15. I believe that after that this will be 100%. Thanks again, sorry for being such a stickler for details.

Lowen