Detect a cell has un-approved change or not?

LongTTH · #1 05-19-2012, 03:48 AM

Hi all,

I'm finding some way to do "highlight anything change with red color"
my thought is :

Code:

loop through all cell in this active sheet
  if (cell has unapproved change) then
      set text color = red
  else 
      set text color = normal;
next cell

My current code is :

Code:

Sub HighlightCellWithUnApproveddChanges()
    Dim rwIndex As Integer
    Dim colIndex As Integer
    For rwIndex = 1 To 10
            For colIndex = 1 To 5
                If Cells(rwIndex, colIndex).XlCellChangeState = XlCellChangeState.xlCellChanged Then _
                    Cells(rwIndex, colIndex).Interior.ColorIndex = 36
            Next colIndex
    Next rwIndex
End Sub

any help are appreciated!
thanks

Colin Legg · #2 05-20-2012, 02:22 AM

Hi,

The xlCellChangedState enumeration constants are used to indicate whether a PivotTable value cell has been edited or recalculated since the PivotTable report was created or the last commit operation was performed, so I don't think it is suitable for your needs.

Please would you describe a bit more about how you envisage the overall effect to work? The key considerations to this are:
(1) From what point are you remembering changes from?
(2) At what point does a synchronisation occur so they are no longer considered to be changes?

Depending on (1) and (2), the most obvious way to do this would be to have a hidden sheet which is identical to the one where the changes occur. When the user changes the sheet, you can have conditional formatting which highlights any differences between the two sheets.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to change number in cell	ubns	Excel	5	05-14-2012 04:21 PM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM
Automatically change the value of one cell so that two other cells become equal	matthew544	Excel	5	09-18-2011 08:56 AM
CHange colour of footer if a cell changes to red	OTPM	Excel	0	05-26-2011 07:15 AM
Is there a way to detect duplicate dates as I enter them?	Karin	Excel	1	03-04-2011 12:56 PM