Highlighting changes in cell *after* data input macro is run

[TiPu1]

Hello All,

I'm not an Excel genius by any means, and I know enough VBA to get me by. I have a quick question for you all and I hope it's an easy solution. I work for a marketing agency where technical savvy-ness is really limited. We analyze a lot of web page elements. I have a program that exports this data into a csv file and I've created a template for the user to add this sheet. This data is primarily text. I've created a button on another sheet that runs a macro that extracts data from the first sheet onto the second sheet in a particular format for ease of reading and editing it. That part is all good, this macro works and the button works. Now I'm trying to have it such when a person edits one of these cells, it highlights the changes on this newly sheet using VBA (the highlighting changes function just turned out to be too messy and buggy for what I want). I have this basic change macro that I have on the sheet currently:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H500"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Interior.ColorIndex = 30
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This works as well. However, my problem is that when I run the data button macro, of course excel reads this as a change to the cell and highlights it as a change before any actual changes are made. How can I have it so I run my data macro, and the cell changes are highlighted only *after* the user goes in to edit the text data plugged in from the data macro? All help is appreciated!

 

[Dreadknight Nasus]

Try adding "Application.EnableEvents = False" at the start of your data button macro. This will stop the worksheet_change macro from responding to the event.

Don't forget to add a matching ... = True at the end to turn it back on.

 

[TiPu1]

Hey Nasus,

Thanks so much, this solved that problem. Now I actually ran into another small snag. I realized my code for the cell highlight highlights the cell even if I just click on it and don't change the text. What can I add to my code to only highlight the cell only if the text in the cell was physically altered from the original text? Thank you so much!

 

[Dreadknight Nasus]

I have no idea... that shouldn't be happening, and it doesn't happen with my instance of your code. The only thing I can think of is that you have Worksheet_Selection_Change instead of Worksheet_Change.