Lookup cell values and display cell

[osx99]

I have long lists of excel data some of which is duplicate. I want to write a macro that runs in excel to do the following but not sure where to start

1.Constantly runs when moving the cell cursor
2.looks up the value of the current cell and displays the cell and row if there is a duplicate in the same column (not including the cell currently selected)
3.Re-runs every time I move the cursor
4. Displays the results in the bottom section of the screen (where ready is shown) without the need to click ok

Can anyone help?

 

[SkipVought]

Hi,

What is the point of, "...Constantly runs when moving the cell cursor..."

Excel has no mouse events for the Spreadhseet.

So you want to SELECT a cell and see the address of any duplicate value? How did you want that displayed to you?



Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Fr33dan]

You'll need to use the Worksheet_SelectionChange will run whenever you select a new cell and Application.StatusBar will let you modify that row at the bottom of the screen

 

[VRoscioli]

Here's something I hacked together real quick... could probably be done more elegantly, though:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim c As Range

If Target.Cells.Count > 1 Then
    Application.StatusBar = "Ready"
    Exit Sub
End If
If Target.Value <> "" Then
    Set c = Target.EntireColumn.Find(Target.Value, , , xlWhole)
    If c.Address = Target.Address Then Set c = Target.EntireColumn.FindNext(c)
    If c.Address = Target.Address Then
        Application.StatusBar = "Ready"
    Else
        Application.StatusBar = c.Address
    End If
Else
    Application.StatusBar = "Ready"
End If


End Sub

 

[osx99]

VRoscioli

Thanks for that it works most of the time but occassionally I get the following error

"Run time error 91

Object variable or with block variable not set"

 

[Fr33dan]

Is something different with the cells when you get the error? Not sure if this'll work but try changing

Set c = Target.EntireColumn.Find(Target.Value, , , xlWhole)

to

Set c = Target.EntireColumn.Find(Target.Value, , xlValues, xlWhole)