VBA to highlight changed cells

[frateg8r]

I have several workbooks that have shipping data that I share with clients; I update the sheets throughout the day and then email them to the client at the end of the day. My goal is to implement a code that will highlight any cells that were changed that day, making it easy for the client to see only what has changed.

I've reviewed and tested the Excel change tracking function - it does not work for this purpose. I do not believe any conditional formatting will work, as this needs to be based on an event handler to the best of my knowledge.

I've perused this forum, and many many Google pages, to no avail. In fact, the closest code I've found to accomplish this is found at Can excel automatically highlight a cell when I make a change in box ? - Yahoo Answers and sadly, even that did not work (it's not quite what I was shooting for, but would have been a nice starting point if even that code had worked in my test.)

So, I am looking for any feedback on a decent starting point to resolve this challenge.

Thanks to all the folks willing to pony up some time and knowledge on my behalf.

Bob

 

[mole999]

may sound simplistic, when you create the page you have a second set of the original data off to oneside, then the CF only has to check between whats dispalyed and whats stored to show differences, no triggers or anything like that

 

[theBardd]

Private Sub Worksheet_Change(ByVal Target As Range)Dim i As Long


    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    With Target
    
        If .Cells.Count = 1 Then
    
            For i = .FormatConditions.Count To 1 Step -1
                
                .FormatConditions(i).Delete
            Next i
            
            .FormatConditions.Add Type:=xlExpression, Formula1:="=TODAY()=DATESERIAL(" & Format(Date, "yyyy\,mm\,dd") & ")"
            .FormatConditions(1).Interior.ColorIndex = 39
        End If
    End With


ws_exit:
    Application.EnableEvents = True
End Sub

 

[frateg8r]

This shows up in conditional formatting rules, and I kind of get where it's going, but the event trigger does not seem to be what I need - I change a cell with existing data, or add data to an existing cell, and nothing happens. What am I missing?

 

[mole999]

I think its the date serial value being used, its about establishing which your system interprets as a today date to display, not sure as yet what it might be

 

[mole999]

There must be a way of shortening all this, but playing has developed

        Dim stuff As Long
        stuff = Now()
        stuff = Int(stuff) - 1  'gives a numeric date value rounded down to todays numeric number

.FormatConditions.Add Type:=xlExpression, Formula1:="=TODAY()=" & stuff & "" 'in replacement for DateSerial

 

[frateg8r]

Success! Thank you mole! Incorporating your fix into theBardd's code did the job!

 

[frateg8r]

So, the code worked on Monday, but once the calendar rolled over to Tuesday, it did not. Which is to say, the Monday highlights disappeared, but new changes did not produce any highlight. I might try tying the code to a cell with today's date in it to see if that will jog the system in the direction I want.

 

[theBardd]

So, the code worked on Monday, but once the calendar rolled over to Tuesday, it did not. Which is to say, the Monday highlights disappeared, but new changes did not produce any highlight.

Isn't that what you wanted?

 

[mole999]

remove the minus one from the vba, i had the same problem, its about getting a stable date value, maybe its rounding up or down, when it should round down to the nearest whole number after midninght which will work all day