Worksheet_Change to Highlight Changes

[VBA Wannabe]

I'm trying to find code (I can't write my own yet) that will highlight any cells that have been changed with fill color and bold font. I've searched around the forum and come up with the code below so far. It's not working - when I make a change to a cell and hit enter, it highlights and bolds the cell BELOW the one that changed! Can anyone help?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.EnableEvents = True
End Sub

Thanks!

 

[schielrn]

Welcome to the Board!

Change your selections to targets. I would bet that if you used tab instead of enter it would highlight the one on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With target.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With target.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.EnableEvents = True
End Sub

Hope that helps.

 

[VBA Wannabe]

Perfect! That worked! I really needed it to work whether the user hits enter or tab. Thank you so much!

 

[VBA Wannabe]

schielrn - I don't understand it! Your code worked perfectly yesterday, I saved the file and closed. Today I went in and it's not working at all. No error message, nothing. The code is still there, this is a 2007 macro enabled .xlsm file. Can anyone tell me why it won't work today?

 

[schielrn]

Your enable events must have gotten turned off somehow maybe?

In the immediate indow type:

application.enableevents = true

and then press enter.

The code may have errored and caused your events to be disabled.

Hope that helps.

 

[VBA Wannabe]

Sorry, I'm truly new to VBA. What else do I have to type besides just opening a module and inserting the following line?

application.enableevents = true

I did that and I'm getting a compile error - invalid outside procedure. The help screen is telling me I need to have this line as part of a sub or function. I tried to put "sub" at the top and got another compile error - expected identifier.

Any help you can provide would be much appreciated!

 

[schielrn]

This doesn't go into a module or anything like that. While in the VBA screen press control+G, that will open the immediate window (also under View-->Immediate Window). Then put that in there and press enter. It will appear as if nothing happened other than your cursor will go to the next line.

Then save the file and re-open it and see if it works.

Hope that helps.

 

[VBA Wannabe]

That's what it was! Thank you - I learned another new thing!

I now realize the reason I originally got an error was because the workbook was protected. So the code couldn't highlight and bold the cell. When I unprotect the workbook, it works like a charm. (After I enable events again)

If you can stand helping me one more time, is there a way to allow these formatting changes to unlocked cells while a workbook is still protected?

 

[schielrn]

You could alwyas unprotect and protect the workbook in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
activesheet.unprotect "password goes here if there is one"
Application.EnableEvents = False
With target.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With target.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.EnableEvents = True
activesheet.protect "password goes here if there is one"
End Sub

Hope that helps.

 

[VBA Wannabe]

Unfortunately, the people whose changes I want to highlight are not people that I want to have unprotected access to the workbook. I don't want to give them the password and I don't want to put the password into the code in case it changes. Any ideas?

(Thank you so much for your continued help on this.)