1

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!!

aduguid
  • 3,099
  • 6
  • 18
  • 37
CHsu
  • 17
  • 6
  • 3
    When you change a cell or a range in your original VBA script, then use the code `.Interior.Color = RGB(255, 253, 130)` to change the color of that cell or range. – JNevill May 07 '18 at 16:57
  • Any reason why you dont simply use Conditional Formatting? – jeffreyweir May 07 '18 at 19:15
  • Jeffrey- Sorry for the late reply. I don't think conditional formatting can perform the task- or do you mind elaborating your idea on that. – CHsu May 08 '18 at 07:56
  • JNevill - there are quite many of them and the task began to seem repetitive, that was why I was thinking if there is an easier way to do it. – CHsu May 08 '18 at 07:57

1 Answers1

0

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
aduguid
  • 3,099
  • 6
  • 18
  • 37