List changes on a New Sheet Private Sub Worksheet_Change

[jamilm]

Gurus,

I have the following code earlier posted by Jeffery Lopez, this code only works in logging changes user made on cell values.

i am looking for a code or modification of the below code that if user changes the cell background it should log that , if it cannot log the background color name changed, at least if it logs the colorindex number changed it would be okay. i would appreciate any help on this.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> varPValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">Dim</SPAN> Errb <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br>****<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrTrap:<br>****<SPAN style="color:#00007F">If</SPAN> Target.Value <> varPValue <SPAN style="color:#00007F">Then</SPAN><br>******<SPAN style="color:#00007F">With</SPAN> Sheets("log").Cells(65000, 1).End(xlUp)<br>********.Offset(1, 0).Value = Application.UserName<br>********.Offset(1, 1).Value = "changed cell"<br>********.Offset(1, 2).Value = Target.Address<br>********.Offset(1, 3).Value = "from"<br>********.Offset(1, 4).Value = varPValue<br>********.Offset(1, 5).Value = "to"<br>********.Offset(1, 6).Value = Target.Value<br>********.Offset(1, 7).Value = "On"<br>********.Offset(1, 8).Value = Now()<br>******<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>ErrTrap:<br>**ErrNum = Err<br>**<br>**<SPAN style="color:#00007F">If</SPAN> ErrNum = 13 <SPAN style="color:#00007F">Then</SPAN><br>****<SPAN style="color:#007F00">'*** Multiple cells have been selected, treat them as one merged group*****</SPAN><br>****<SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>**<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>****varPValue = Target.Value<br>****<br>****<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Smitty]

There's no native event to monitor fill color changes, but Jaafar Tribak did come up with a workaround here: http://www.mrexcel.com/forum/excel-...l-color-change-visual-basic-applications.html

Just don't expect it to be hugely efficient, especially if you have a large range.

 

[jamilm]

Thanks Smitty. i tried the code referred from Jaafar. but one problem, it does not store the logs of changes made. perhaps i am missing something. grateful if you could help.

thanks.

There's no native event to monitor fill color changes, but Jaafar Tribak did come up with a workaround here: http://www.mrexcel.com/forum/excel-...l-color-change-visual-basic-applications.html

Just don't expect it to be hugely efficient, especially if you have a large range.

 

[Smitty]

If I recall from briefly looking at it (I have no reason whatsoever to use it), he is informing of the changes via message box, so you'd need to adapt that part.