Highlight *all* changed cells after entry?

[Christian Jones]

Actually, this isn't quite what I'm looking for. Thanks anyway; I'll try
to clarify. I want to "highlight" (that is, say, change the interior color
of) cells that actually have values changed in a recalculation after
Calculate() is done automatically by manually entering a value.

This is the (obviously non-working) first attempt---I find even failed code
gives a better idea of what I want than trying to explain it:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 8
End Sub

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
For Each cell In Range("A1:I1000")
Dim oldval As Range '(???)
Set oldval = cell.Value
cell.Calculate
If oldval = cell.Value Then
cell.Interior.ColorIndex = 8
End If
Next
Application.EnableEvents = True
End Sub

Thanks for taking a look at this; if you can fix it, great---if not, does
anyone have a better approach?
CDJ
Christian Jones

"David Hager" <10407...@compuserve.com> wrote in message
news:#8yarOL2$GA.240@cppssbbsa05...
> It is unclear what type of highlighting you want, but you can use this
event
> procedure in a worksheet module as a starting point.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> On Error Resume Next
> ActiveCell.Dependents.Select
> End Sub
>
> Be aware, though, that if the default action of the cursor is set to move
> after an entry, this method will not work.
>
> David Hager
> Excel MVP
>
> Christian Jones wrote in message ...
> >I'm trying to highlight every cell that changes *value* from an entry
> (i.e.,
> >the manually changed cell and all cascading dependent cells). All
original
> >changes are made by hand, not an external program.
> >
> >Example:
> >a1 has value 1
> >b2 = a1+1
> >c3 = IF(a1 > 1, 1, b2 / 2) [=1]
> >
> >If I change a1 to 2, the new values should be:
> >a1 = 2
> >b2 = 3
> >c3 = 1
> >
> >I'd then also like a1 and b2 to be highlighted, but not c3.
> >Hope that makes sense. Li'l help? Thanks,
> >Christian Jones
> >
> >
>
>

[David McRitchie]

You requirement to show all dependencies is going to
fill up the entire sheet with colored cells, unless you reset
the color. I had thought that conditional formatting would
get close to what you wanted with a copy of the sheet, but
C.F. does not allow access to another sheet.

The requirement to not show a dependent change with
a condition that results in no change is the zinger.

Are you familar with the tool buttons for
Trace Precedents
Trace Dependents

Which seems a lot more useful because it indicates where
the dependencies are.

HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm

Christian Jones <chj...@innocent.com> wrote in message
news:ezOJ1GP2$GA....@cppssbbsa02.microsoft.com...

[Christian Jones]

Yes, I'm familiar with them, but it's way too much information.
Essentially, what I'm looking for is a way input a tiny bit of data, and
then act (not in excel) on all the effects that input has. Considering a
single input generally changes 5-10 other cells (while around 800 are
actually dependent on it), I can't figure out another way to do it. Thanks
for the help anyway!
CDJ

"David McRitchie" <DMcRi...@msn.com> wrote in message
news:uXAiFSP2$GA....@cppssbbsa02.microsoft.com...

[Jake Marx]

Hi Christian,

Here's some code I came up with that _may_ be what you're looking for. It's
not perfect, but it should get you started in the right direction. Let me
know if you have any questions or if it's not what you're looking for.

Regards,
Jake Marx

Private mvValues As Variant

Private Sub Worksheet_Calculate()
Dim vNew As Variant
Dim lRow As Long
Dim nCol As Integer

With UsedRange
vNew = .Value
.Interior.ColorIndex = -4142
End With

If IsEmpty(mvValues) Then GetValues

On Error Resume Next
For lRow = 1 To UBound(vNew, 1)
For nCol = 1 To UBound(vNew, 2)
If vNew(lRow, nCol) <> mvValues(lRow, nCol) Then _
Cells(lRow, nCol).Interior.ColorIndex = 8
Next nCol
Next lRow
On Error GoTo 0

GetValues
End Sub

Private Sub GetValues()
mvValues = UsedRange.Value
End Sub

Christian Jones <chj...@innocent.com> wrote in message

news:#dzOncP2$GA....@cppssbbsa02.microsoft.com...

[Christian Jones]

Private mvValues As Variant

Private Sub Worksheet_Calculate()
Dim vNew As Variant
Dim lRow As Long
Dim nCol As Integer

With UsedRange
vNew = .Value
.Interior.ColorIndex = -4142
End With

If IsEmpty(mvValues) Then GetValues

On Error Resume Next
For lRow = 1 To UBound(vNew, 1)
For nCol = 1 To UBound(vNew, 2)
If vNew(lRow, nCol) <> mvValues(lRow, nCol) Then _
Cells(lRow, nCol).Interior.ColorIndex = 8
Next nCol
Next lRow
On Error GoTo 0

End Sub

Private Sub GetValues()
mvValues = UsedRange.Value
End Sub

Sub Changes_Noted()
'
' Changes Macro
' Updates mvValues with new edits/recalcs
'
GetValues
Worksheet_Calculate
'
End Sub

[Christian Jones]

Wow, Jake! Almost exactly what I was looking for. I've included my (very
slightly) modified code below. The only changes I made were to not reset
after each entry (I want the opportunity to change a couple of cells at a
time, to see how the changes interact), and then to make a quick-and-dirty
macro I can assign to a button that says "I've noted the changed cells---go
ahead and reset".

Thanks again, Jake!
CDJ

"Jake Marx" <Jak...@home.com> wrote in message
news:Osd0RwP2$GA.87@cppssbbsa05...

> Hi Christian,
>
> Here's some code I came up with that _may_ be what you're looking for.
It's
> not perfect, but it should get you started in the right direction. Let me
> know if you have any questions or if it's not what you're looking for.
>
> Regards,
> Jake Marx
>
>

> Private mvValues As Variant
>
> Private Sub Worksheet_Calculate()
> Dim vNew As Variant
> Dim lRow As Long
> Dim nCol As Integer
>
> With UsedRange
> vNew = .Value
> .Interior.ColorIndex = -4142
> End With
>
> If IsEmpty(mvValues) Then GetValues
>
> On Error Resume Next
> For lRow = 1 To UBound(vNew, 1)
> For nCol = 1 To UBound(vNew, 2)
> If vNew(lRow, nCol) <> mvValues(lRow, nCol) Then _
> Cells(lRow, nCol).Interior.ColorIndex = 8
> Next nCol
> Next lRow
> On Error GoTo 0
>

> GetValues

> End Sub
>
> Private Sub GetValues()
> mvValues = UsedRange.Value
> End Sub
>
>

[Myrna Larson]

On Sun, 18 Jun 2000 10:27:41 -0400, "Christian Jones" <chj...@innocent.com>
wrote:

Just a couple of comments:

I can't get UsedRange by itself to work. I have to write
ActiveSheet.UsedRange. Does it work as written for you?

Jake's code assumes that the used range begins at A1. (On the first pass
through the looks, it is looking at Cells(1, 1).) That's a reasonably safe
assumption, but not totally foolproof. I've gotten "bit" by this problem a
couple of times. If there's any possibility that it won't begin at A1, inside
of the For/Next loops you could change

Cells(lRow, nCol)

to

Activesheet.UsedRange.Cells(lRow, nCol)

or in the first block, write

With Range("A1", Cells.SpecialCells(xlLastCell))

>Private mvValues As Variant
>
>Private Sub Worksheet_Calculate()
> Dim vNew As Variant
> Dim lRow As Long
> Dim nCol As Integer
>
> With UsedRange
> vNew = .Value
> .Interior.ColorIndex = -4142
> End With
>
> If IsEmpty(mvValues) Then GetValues
>
> On Error Resume Next
> For lRow = 1 To UBound(vNew, 1)
> For nCol = 1 To UBound(vNew, 2)
> If vNew(lRow, nCol) <> mvValues(lRow, nCol) Then _
> Cells(lRow, nCol).Interior.ColorIndex = 8
> Next nCol
> Next lRow
> On Error GoTo 0
>

>End Sub
>
>Private Sub GetValues()
> mvValues = UsedRange.Value
>End Sub
>
>

[Christian Jones]

Yup, it works as written for me. Thanks for the heads up on Cells(,),
though---it doesn't apply here, but I can see that as a potential problem.

"Myrna Larson" <myrna...@home.net> wrote in message
news:3otpksoopfeq8lh5o...@4ax.com...

[Jake Marx]

Hi Myrna,

Since the code resides in a class module (behind the Worksheet), any
unqualified objects, methods, or properties are inherently qualified by that
Worksheet. It's the same as on a UserForm - code behind the UserForm
doesn't need to use Me.<method> each time - <method> will work fine. I'm
not sure why this didn't work for you.

Thanks for the tip on qualifying Cells with the UsedRange - I hadn't thought
of that.

Regards,
Jake Marx

Myrna Larson <myrna...@home.net> wrote in message
news:3otpksoopfeq8lh5o...@4ax.com...
> On Sun, 18 Jun 2000 10:27:41 -0400, "Christian Jones"
<chj...@innocent.com>
> wrote:
>
> Just a couple of comments:
>
> I can't get UsedRange by itself to work. I have to write
> ActiveSheet.UsedRange. Does it work as written for you?
>
> Jake's code assumes that the used range begins at A1. (On the first pass
> through the looks, it is looking at Cells(1, 1).) That's a reasonably safe
> assumption, but not totally foolproof. I've gotten "bit" by this problem a
> couple of times. If there's any possibility that it won't begin at A1,
inside
> of the For/Next loops you could change
>
> Cells(lRow, nCol)
>
> to
>
> Activesheet.UsedRange.Cells(lRow, nCol)
>

<snip>