need help with syntax of a Workbook Event

[Paul Simon]

I wrote a Workbook Event with the following purpose in mind. If a
user opens an existing workbook and makes any changes, the font color
of the altered cell(s) is changed to red.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Not Application.Intersect(Target, Cells) Is Nothing Then
ActiveCell.Font.ColorIndex = 3
End If
End Sub


My syntax is somehow flawed because it behaves differently depending
on how the "Move Selection After Enter" option is set in
Tools>Options>Edit.

For example, on my PC, I have that option unchecked so that when I hit
Enter, the cellpointer remains in the current cell. In that scenario,
my Workbook Event works perfectly in that if I make a change to a cell
and hit Enter, that cell's font correctly changes to red.

However, if the "Move Selection After Enter" option is set to Down, my
Workbook Event behaves differently. In that case, it's the font of
the cell where the cellpointer comes to rest that is changed rather
than the cell that was altered. For example, if I make a change to
cell A2 and hit Enter, the cellpointer will go down to A3. However,
instead of cell A2 changing to red, cell A3 is incorrectly changed to
red.

This also occurs if you have the "Move Selection After Enter" option
unchecked but after you make a change to a cell, you hit a movement
key (like the down arrow) rather than Enter.

In other words, it appears the any cellpointer movement is executed
before the Worksheet Event is triggered.

Several employees are using this Workbook Event, and all have
different options set for the Enter key and/or use an arrow key rather
than the Enter key. Getting all of them to change in this regard
would solve this problem but create others and just isn't practical.

If anyone can come up with a cure to this ill-behaving Workbook Event,
I'd appreciate it very much.

Many thanks,
Paul

 

[Dick Kusleika]

Paul

Target is the variable that refers to the Range that is changed. Because
this event fires AFTER a change is made, the activecell may not be the same
as the cell that has changed. Change ActiveCell to Target and it should
work as expected.

Also, I'm curious what the Intersect condition does for you. It seems that
you are testing that the Target is on the ActiveSheet, but I don't know how
it could be any different.

 

[steve]

Paul,

Look at the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
* your code *
End Sub

This should work with Enter or Tab.
And get the correct cell.

steve

 

[Paul Simon]

Thanks to all of you for taking the time to respond to my question.

Dick, thanks so very much for clearing this up for me. Based on your
response, I got rid of the unnecessary Intersect line and changed
Activecell to Target, resulting in this code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Target.Font.ColorIndex = 3
End Sub

It now works absolutely perfectly under all situations! Thanks again,
Dick!


Steve, thanks for the suggestion as well. Unfortunately, based on
other requirements, I must use a Workbook Event rather than a
Worksheet event.

Again, my appreciation to all.
Best regards,
Paul