I have found several examples for checking whether the value entered into a cell is equal to that in another cell and if not displaying a Message Box.
What I am struggling with is how to extend this to a range (array) of cells.
For example, if the value entered into cell A1 equals data already in cells C1, D1 and E1 then a Message should be displayed saying something like ‘number already used.’
The code should also be capable of performing the same check when entering data in cells A2, A3, etc.
Please can anybody help me.
Thank you.
You could try something like this?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lrow As Long lrow = Cells(Rows.Count, 1).End(xlUp).Row Set r = Range("A1:A" & lrow) If Target.Count > 1 Then Exit Sub For Each cell In r If cell.Value = cell.Offset(, 2).Value Or _ cell.Value = cell.Offset(, 3).Value Or _ cell.Value = cell.Offset(, 4).Value Then MsgBox "This Value was already used" Application.EnableEvents = False cell.Select cell.Interior.ColorIndex = 6 End If Next cell Application.EnableEvents = True End Sub
Last edited by realniceguy5000; 07-20-2011 at 01:02 PM. Reason: Change Script:
Thank You, Mike
Some Helpful Hints:
1. New members please read & follow the Forum Rules
2. Use Code Tags...Place[code]Before the first line and[/code] After the last line.
3. If you are pleased with a solution mark your post SOLVED.
4. Thank those who have help you by clicking the scales at the top right of the post.
Here...
Thank you for this.
I have a further question, can the code be expanded so that data entered in columns A and B for example are checked against data in C, D and E.
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks