[gazzae]

ok we will need to use a module to take advantage of public variables. So you will need to add one by, in the visual basic editor, "Insert, Module". open the module and enter the following...

'***THE TWO PUBLIC VARIABLES FOR OUR ENTER AND EXIT VALUES
Public StrEnterValue As String
Public StrExitValue As String


Sub entercell()

'***ASSIGN VARIABLE THE VALUE OF THE CELL WE HAVE JUST ENTERED***
StrEnterValue = ActiveCell.Value

End Sub

Sub exitcell(row As Integer, column As Integer)

'***if the value is nothing then exit***
If StrEnterValue = "" Then
    End
End If

'***ASSIGN VARIABLE THE VALUE OF THE CELL WE HAVE JUST EXIT***
'***CHANGE SHEETNAME TO MATCH YOURS!!!!
StrExitValue = Worksheets("sheet1").Cells(row, column).Value

'***IF THE VALUES ARE DIFFERENT THEN SET THE COLOUR OF THE CELL TO RED***
If StrEnterValue <> StrExitValue Then
'***CHANGE SHEETNAME TO MATCH YOURS!!!!
    Worksheets("sheet1").Cells(row, column).Interior.ColorIndex = 3
End If

End Sub

We will also need to change the sheet so that it calls these subs, so again in the visual basic editor click on the sheet you want this to happen on and enter the following - remove any code you had previously.

Sub Worksheet_Change(ByVal Target As Range)

Dim introw As Integer
Dim intcolumn As Integer

'***GET ROW AND COLUMN FOR THE CELL THAN HAS JUST BEEN CHANGED***
introw = Target.row
intcolumn = Target.column

'***CALL EXITCELL PASSING IT THE ABOVE VARIABLES***
Call exitcell(introw, intcolumn)

End Sub

Sub Worksheet_SelectionChange(ByVal Target As Range)

Call entercell

End Sub

What happens with this code is that when you enter the cell it records the value and then compares it to the value when you exit the cell. If the values do not match then it sets the cells background to red.

I have also put in that if the cell doesn't have a value when you enter it then it doesn't set it to red. But you can remove this is you don't want it.


If you PM me your email I'll send you a workbook that has this working in it.
__________________

Originally Posted by absthechatter

That would be brilliant, thank you.

One other question (probably not the last!!) on some machines, that code works perfectly, on others I get runtime error '1004': Unable to set the ColorIndex property of the interior class. Debug does not appear to help. They are all running same versions of software. Any suggestions?

Many thanks again

Abs

Usually if the cell is protected you would get this message.