MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

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