Microsoft Excel Help Needed

[absthechatter]

Is there any way, possibly using conditional formatting that I can get Excel to show when anyone has made any changes in cells. I do not really want to use the track and review changes function as I do not want to create shared workbooks.

Any help much appreciated.

Many thanks

Abs

[Maroon]

I guess there's lots of options but would ideally need to know a bit more to see how complex it needs to be.

One suggestion, why not have the original values in one column (either protected or hidden), and a second set of values that can be changed in the next column? Then maybe an extra column using a simple IF to flag which ones have changed?

[absthechatter]

Thanks for that.


What I am doing is sending out a spreadsheet to several thousand sites. The spreasheet consists of about 10 worksheets with about 20 columns in each. Most of the information is filtered and chosen from a picklist. There will be 10 different people quality assuring the information that comes back. They only have a short time to do it and what I wanted was for them to be able to see at a glance, any information that had been changed - possibly by highlighting it in a different colour. I wanted to avoid additional columns if possible.
I know I can use conditional formatting to change the colour and I could use the "equal to" to show if each cell has been changed. But can I put some formula in to show something like "equal to whatever the contents of cell are" so I don't have to individually put the formatting in each cell?

Many thanks

Abs

[gazzae]

Stick this into the worksheet change function...


ActiveCell.Offset(-1, 0).Interior.ColorIndex = 3

Whenever a cell is changed that will make the cell red.

You may need to play about with it as if they click on a cell to change it and instead of hitting enter they click on another cell it will change the wrong cell red. But at least it gives you a starting point.
__________________

actually, ignore that, here is how to do it properly.

Put this under worksheet change, it will chage the correct cell.

strrow = Target.Row
strcolumn = Target.Column
Cells(strrow, strcolumn).Interior.ColorIndex = 3

[absthechatter]

Is that the Track Changes,Highlight Changes field? If it is I keep getting the text is not a valid reference or defined name. It highlights the ActiveCell.Offset bit.

Abs

[gazzae]

Neither, its in the visual basic editor.

Press Alt + F11

Select worksheet from the first drop down menu and then change from the drop down beside it. Paste the second lot of code in there.

[absthechatter]

Originally Posted by gazzae

Neither, its in the visual basic editor.

Press Alt + F11

Select worksheet from the first drop down menu and then change from the drop down beside it. Paste the second lot of code in there.

That seems to do the trick nicely. Thank you very much.

But just to be awkward!!!!! I know some of the people that will be completing this sheet. Using that method, if I overwrite with the same information, the box will still change red. Is there anyway of stopping that?

Many thanks

Abs

[gazzae]

Yes, you would have to get the value when you enter the cell, stick it in a public variable and then compare it with the value when you exit the cell.

If you want I can write it for you after lunch.

[absthechatter]

Originally Posted by gazzae

Yes, you would have to get the value when you enter the cell, stick it in a public variable and then compare it with the value when you exit the cell.

If you want I can write it for you after lunch.

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

[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.

[absthechatter]

Thank you very much indeed. I will give that a try.

[gazzae]

Not a problem mate.

Of course the big problem with the code above is that the user can simply change the background colour back to what it was.

If your really need to track changes then the best way would be to use a hidden sheet and record the cells and the values changed there.