Paste Once No Format Change Help

  • Thread starter Thread starter CCorreia
  • Start date Start date
C

CCorreia

Hello,

I'm currently using the following in a spreadsheet to flag data that is
changed in any cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Cells.Font.ColorIndex = 3 'ColorIndex = 3 = Red
End Sub

I would like to change this to only become active if a cell already has data
in it, prior to editing. I assume this would require a VLOOKUP expression but
I cannot figure out how to write it. Any assistance would be appreciated.

Thank you.
 
Worksheet change only runs after the data has been changed to you can't tell
if data was in the cell previously. You need another cells that contain s
the previous data. For example, if you change A1 check B1 to see if it is
empty. If B1 is empty copy the data from A1 to B1. Then use B1 in your
spreadsheet s the final value.
 
Try the following code. Whenever you select a (single) cell, it stores the
value of that cell in a variable. The Worksheet_Change event then looks at
that variable to see if the cell was empty before it was changed. Seems to
work pretty well for individual cells but not for mutiple-cell ranges.

Dim PrevVal

Private Sub Worksheet_Change(ByVal Target As Range)
If Len(PrevVal) > 0 Then
Target.Cells.Font.ColorIndex = 3 'ColorIndex 3 = Red
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
'Store the value of the currently selected cell.
PrevVal = Target.Value
'If Target is empty, rest its font color.
If Len(Target.Value) = 0 Then
Target.Cells.Font.ColorIndex = xlAutomatic
End If
End Sub

This code all goes in the code module for the worksheet where you want this
to work.

Hope this helps,

Hutch
 
Back
Top