Cable Forum - Microsoft Excel Help Needed

Cable Forum (https://www.cableforum.uk/board/index.php)

- General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)

- - Microsoft Excel Help Needed (https://www.cableforum.uk/board/showthread.php?t=29287)

Microsoft Excel Help Needed

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

Re: Microsoft Excel Help Needed

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?

Re: Microsoft Excel Help Needed

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

Re: Microsoft Excel Help Needed

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

Re: Microsoft Excel Help Needed

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

Re: Microsoft Excel Help Needed

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.

Re: Microsoft Excel Help Needed

Quote:

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

Re: Microsoft Excel Help Needed

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.

Re: Microsoft Excel Help Needed

Quote:

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

Re: Microsoft Excel Help Needed

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

Code:

'***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.

Code:

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

Quote:

Originally Posted by absthechatter

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

Re: Microsoft Excel Help Needed

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

Re: Microsoft Excel Help Needed

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.