How to enter new data and change also the cell color?
Posted by Eli Weiss on September 03, 2001 1:54 AM
Hello everybody,
I am using long databases which I am updating from to time by overwriting the old data by new one.
I am looking for a way to be sure that I have swiched all the old data by the new one.
Is there any simple way to enter a new data into a cell in Excell so that it will overwrite the old data but in the same time it will change the color of the cell to a specified color (e.g. yellow), which will indicate that new data has been entered?
A simple and interactive way will be appreciated.
Eli
Posted by steve w on September 03, 2001 10:17 AM
Try this
in your worksheet module place this code
it will change the color to red after a change
change range and color to suit
the original code was given to me by someone on this board and I've modified it to suit your needs
heres the code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangeRange As Range
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set ChangeRange = Range("a1:z30") 'This is the range you want to be colored when theres a change
If Not Intersect(Target, ChangeRange) Is Nothing Then
Application.ScreenUpdating = False
Target.Interior.ColorIndex = 3 'this is the color it will change to
End If
Application.ScreenUpdating = True
Set ChangeRange = Nothing
End Sub
in your workbook module place this code
it will change the color back to none when the workbook is opened
change page and range to suit
Private Sub Workbook_Open()
Sheet1.Range("a1:z30").Interior.ColorIndex = xlNone
End Sub
hope this helps
steve w
Posted by Eli Weiss on September 03, 2001 10:51 PM
Fantastic! Thank you.
Hi Steve,
It works fine
Thank you again
Eli
