I am new to VBA so please bear with me if I sound stupid-
Background info: I am trying to automate a workbook that we update every quarter- I want to show which cells are changed by my VBA script by changing the background colour of those cells, so that I can track them while I develop the script. It is also for a colleague, who has zero knowledge of VBA and will review the figures and continue updating the file manually.
Now I noticed that there is a event procedure that I can use:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = RGB(255, 253, 130)
End Sub
As I understand, it can mark cells even if the changes are done by VBA, but I don't know how to implement it with my VBA script. Do I somehow insert this in my general procedure or do I have to embed this to the target file that is updated?
Please note that the VBA is not written in the target file. The file cannot be in xlsm format due to client request.
Thanks in advance!!
I would create an Excel Add-In for this and install it to your colleagues machine. The code would then be separated from the client file as per your requirement.
You can use a .bat file to copy it out to their addins folder.
REM
REM /E = Copies directories and subdirectories, including empty ones. Same as /S /E. May be used to modify /T.
REM /D:m-d-y = Copies files changed on or after the specified date. If no date is given, copies only those files whose source time is newer than the destination time.
REM /K = Copies attributes. Normal Xcopy will reset read-only attributes.
REM /Q = Does not display file names while copying.
REM /R = Overwrites read-only files.
REM /Y = Suppresses prompting to confirm you want to overwrite an existing destination file.
REM
XCOPY ".\YourAddin.xlam" "%AppData%\Microsoft\AddIns\YourAddin.xlam" /E /K /Q /R /Y /D