From: "Rob Bruce"<rob@rb-ad.dircon.co.uk> 
References: <81jt3f$sg3$1@trex.antw.online.be>
Subject: Re: Checking cells on contents
Date: Thu, 25 Nov 1999 21:24:27 -0000
Newsgroups: microsoft.public.excel.programming

Marc,

Try this:

You will need to run the ssetUpOnEntry initially. This will lead to the
sCheckCellEntry procedure running whenever an entry is made in any cell
within any Excel worksheet. ssetUpOnEntry might be called from an auto_open
routine.

When you no longer need the sCheckCellEntry routine to execute, run the
sKillOnEntry routine (in auto_close?).

Note that the OnEntry event is hidden in Excel 97 and later, having been
replaced by what Microsoft considers to be more advanced and adaptable
possibilities using application level objects declared WithEvents. This, I
think, is a case where doing things 'the old way' is perfectly acceptable.

Option Explicit
Private rngActiveCell As range

Sub ssetUpOnEntry()
    '   sets up the procedure to run when
    '   a value is added to a cell - much easier
    '   than coding an application level on entry
    '   event...
    Application.OnEntry = _
        ThisWorkbook.Name & "!sCheckCellEntry"
End Sub

Sub sCheckCellEntry()

    Dim rngfind     As range
    Dim wstActive   As Worksheet

    '   set some object variables:
    set wstActive = ActiveSheet
    '   This will save the activecell 
    '   in case the user has Move On Enter
    '   set...
    set rngActiveCell = ActiveCell

    '   Try to find a duplicate...
    set rngfind = wstActive.Cells. _
        find(ActiveCell.Value, ActiveCell, _
                                , xlWhole)
    '   Did we find anything?..
    If Not TypeName(rngfind) = "Nothing" Then
        '   We may have found the cell we just
        '   entered a value into!..
        If Not rngfind.Address = _
                    ActiveCell.Address Then
            '   Display a message...
            MsgBox "Duplicate on current worksheet at " _
                        & rngfind.Address, vbExclamation
            '   The selection may move after enter: we
            '   need to re-select the previously
            '   active cell...
            If Application.MoveAfterReturn Then _
                Application.OnTime Now(), _
                    ThisWorkbook.Name & _
                        "!sResoreActiveCell"
        End If
    End If

End Sub

Sub sResoreActiveCell()
    '   Runs if we need to re-select
    '   a previously active cell
    '   where the user has the Move On
    '   Entry Option selected...
    rngActiveCell.Select
End Sub

Sub sKillOnEntry()
    '   Gets rid of the OnEntry event
    Application.OnEntry = ""<vheuckem@glo.be>
End Sub


www.rb-ad.dircon.co.uk/rob/excelvba/


Marc Van Heucke  wrote in message

Can anyone help me writing a code to check if the value of one cell is
also on another cell in the same sheet.

There are a few numbers that I have to keep an eye on, so what I want is
the following. By entering the value that already exists on the same sheet,
excel have to warn me about it.

Suppose I have several blocks of cells with certain data in it. On these blocks has to be a check.