Showing any changes on tab

[tasander]

Hi

I have a tab within a workbook where i want the user to copy a different spreadsheet into and then press a macro button to run some code, however after this button has been pressed i want this one tab to show any changes that have been made to it, i.e make the cell red or something just so i can keep track of manual adjustemnts.

Can anyone help??

Thanks

 

[lenze]

Hi and Welcome to the Board!!
For manual adjustments

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   .Font.ColorIndex = 3
   .Font.Bold = True
End With
End Sub

You might, however, want to use a Comment to track changes

<a href="http://vbaexpress.com/kb/getarticle.php?kb_id=909"> Tracking Changes With Comments<a>

lenze

 

[tasander]

Thanks, that is really good

Cheers

 

[tasander]

Hi

Going back to this question, it works fine on all data but when the cell has #N/A or #VALUE! then it causes a break, do you know anyeay to get round this.

Thanks

 

[lenze]

ADVERTISEMENT

Hi

Going back to this question, it works fine on all data but when the cell has #N/A or #VALUE! then it causes a break, do you know anyeay to get round this.

Thanks

I'm not following you. If you are entering data and using the WorkSheet_Change Event, how does the cell get a #N/A or #VALUE?

lenze

 

[tasander]

Hi

sorry i'm not being too clear, i paste in a spreadsheet and then run a macro which at the end of the macro sets a variable to true so that the code you supplied knows to run whenever data is entered/changed.

The problem is that when the sheet is copied in, in the first place it usually has some erroneous data such as #N/A. i can do a search and replace on all these peior but would prefer some code to skip around these values.

Hope this makes more sense

Thanks

 

[lenze]

ADVERTISEMENT

Post the code you currently are using. Also, the type of Data (Numbers?,Text? etc)
lenze

 

[tasander]

Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)


If Range("p1").Value = "Track" Then
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
With Target
.Font.ColorIndex = 7
.Font.Bold = True
End With

Else
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Range("p1").Value = "Track" Then


If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If


Else
End If

End Sub

At the end of the macro i make cell P1 = "Track"

Everything works fine after that apart from when i click on a cell which contains: #VALUE! or #N/A (i.e calculations which are invalid or lookups which haven't worked)

 

[lenze]

I don't understand the reason for P1="Track". Anyway, try adding this line at the beginning of both modules

If IsError(Target) Then Exit Sub

lenze

 

[tasander]

Thanks, that works

The P1 reference is because i don't want the sheet to track changes all the time just some designated times