Compare Entry to Other Cells

A

aboly8000

Board Regular
Joined
Sep 4, 2019
Messages
59
Joe4 said:
OK. Try this. I put all the ranges that may change at the very top as range variables. So those should be the only three things in the code that you may need to change.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng1 As Range
    Dim rng2 As Range
    Dim isect As Range
    Dim isect2 As Range
    Dim cell As Range
    Dim dd() As Variant
    Dim i As Long
    Dim mtch As Boolean
    Dim msg As String
    Dim myEntries As String
    Dim ddRange As Range
    
'***Set column E validation range
    Set rng1 = Range("E7:E12")
'***Set column A validation range
    Set rng2 = Range("A1:A100")
'***Set drop-down list value range
    Set ddRange = Sheets("Sheet1").Range("A1:A3")
    
    
'   See if any updated cells fall in column E range
    Set isect = Intersect(rng1, Target)
'   See if any updated cells fall in column A range
    Set isect2 = Intersect(rng2, Target)
    
'   Exit if updated cells do not fall in either validation range
    If (isect Is Nothing) And (isect2 Is Nothing) Then Exit Sub
    
    Application.EnableEvents = False


'   First check (column E)
    If Not isect Is Nothing Then
'       Build array of drop-down values
        ReDim dd(ddRange.Cells.Count)
        i = 0
        For Each cell In ddRange
            dd(i) = cell.Value
            i = i + 1
        Next cell
    
'       Loop through all intersecting cells
        For Each cell In isect
'           See if cell entry matches any drop-down values
            mtch = False
            For i = LBound(dd) To UBound(dd)
                If cell.Value = dd(i) Then
                    mtch = True
                    Exit For
                End If
            Next i
'           If value is not in list, erase and return message
            If mtch = False Then
                cell.ClearContents
                msg = msg & cell.Address(0, 0) & ","
            End If
        Next cell


'       Build string of validation entries
        For i = LBound(dd) To UBound(dd)
            myEntries = myEntries & dd(i) & ","
        Next i
        myEntries = Left(myEntries, Len(myEntries) - 1)
    
'       Reset validation
        With rng1.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=myEntries
        End With
    
'       Return message, if necessary
        If Len(msg) > 0 Then
            MsgBox "Invalid entries in cells: " & vbCrLf & Left(msg, Len(msg) - 1), vbOKOnly, "ERROR!"
        End If
    End If
    

'   Second check (column A)
    If Not isect2 Is Nothing Then
'       Loop through all intersecting cells
        For Each cell In isect2
            If (Len(cell) > 0) And (Len(cell) <> 11) Then
                cell.ClearContents
                msg = msg & cell.Address(0, 0) & ","
            End If
        Next cell
            
'       Reset validation
        With rng2.Validation
            .Delete
            .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
                Operator:=xlEqual, Formula1:="11"
        End With
    
'       Return message, if necessary
        If Len(msg) > 0 Then
            MsgBox "Invalid entries in cells: " & vbCrLf & Left(msg, Len(msg) - 1), vbOKOnly, "ERROR!"
        End If
    End If
    
    Application.EnableEvents = True

End Sub
Click to expand...

hi
I want to display an error message for range ("E7: E12"), whenever the value is one with columns ("F7: F12") or ("G7: G12") or ("H7: H12") or ("I7: I12").
For example, when E7 is equal to F7 or G7 or H7 or I7, display an error message.
please help
 
Sort by date Sort by votes
Joe4 said:
Does it need to be added to the original code there, so the code does BOTH things?

Do you really mean "clear contents" and not "delete". To deleting cells, rows, columns usually involves in shifting all the other cells, rows, columns around.
I am guessing that you mean that if a value in E7:E12 is cleared, you want to clear other cells.
So, are you saying if the value in E7 is cleared, that all values in F7, G7, H7, and I7 shouild also be cleared?
Click to expand...

yes "clear contents"
I want the existence of F7 depends on the existence of E7 and the existence of G7 depends on the existence of F7 and the existence of H7 depends on the existence of G7 and the existence of I7 depends on the existence E7
 
Last edited:
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
OK, please spell it out in detail.
Are you just concerned if the value in column E is cleared, or if the other columns are cleared?

Or are you saying the following:
- If the value in column E is cleared, then clear the values in columns F-I (of the same row)
- If the value in column F is cleared, then clear the values in columns G-I (of the same row)
- If the value in column G is cleared, then clear the values in columns H-I (of the same row)
- If the value in column H is cleared, then clear the values in columns I (of the same row)
(so if you clear any cell in columns E-H, it will clear the rest of the columns to the right in the same row)?

Is that what you are trying to do?
 
Upvote 0
yes i want do this
- If the value in column E is cleared, then clear the values in columns F-I (of the same row)
- If the value in column F is cleared, then clear the values in columns G-I (of the same row)
- If the value in column G is cleared, then clear the values in columns H-I (of the same row)
- If the value in column H is cleared, then clear the values in columns I (of the same row)
 
Upvote 0
Try this macro (Supposing your data start with the cells (a1) )
Code: 
Option Explicit
Sub del_to_Last_Column()
Dim Rg As Range, R_Empty As Range
Dim RO%
 
 Set Rg = Range("A1").CurrentRegion
 
 For RO = 1 To Rg.Rows.Count
   Set R_Empty = Rg.Rows(RO).Find(vbNullString)
   If Not R_Empty Is Nothing Then
    R_Empty.Resize(, Rg. _
    Columns.Count - R_Empty.Column + 1) = _
    vbNullString
   End If
 Next
End Sub
 
Last edited:
Upvote 0
If I am understanding you correctly, try this. I think it will do all that you want.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r As Long, c As Long
    Dim i As Long
    
'   See if any cells updated in range E7:H12
    Set rng = Intersect(Target, Range("E7:H12"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in E7:H12
    For Each cell In rng
        c = cell.Column
        r = cell.Row
        Application.EnableEvents = False
'       See if value is added or removed
        If cell = "" Then
'           What to do if value removed (clear columns to right)
            Range(Cells(r, c + 1), Cells(r, 9)).ClearContents
        Else
'           Check to do if value added (check to see if rest of columns match)
            For i = (c + 1) To 9
                If cell = Cells(r, i) Then
                    cell.ClearContents
                    MsgBox "Value in cell " & cell.Address(0, 0) & _
                        " cannot match any value in " & Cells(r, i).Address(0, 0), vbOKOnly, "ENTRY ERROR!"
                End If
            Next i
        End If
        Application.EnableEvents = True
    Next cell

End Sub
 
Upvote 0
ِanother macro with Worksheet_Change Event

Code: 
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False


If Not Intersect(Target, Range("A1:H8")) Is Nothing Then
   If Target.Count = 1 Then
  Call del_to_last_Column(Target.Row, Target.Column)
   End If
End If


Application.EnableEvents = True
End Sub
'================================
Sub del_to_last_Column(My_Row, My_Col)
Dim My_rg As Range, R_Empty As Range
Dim Col%
    
    Set My_rg = Range("A1:H8").Rows(My_Row)
    Col = My_rg.Columns.Count
    Set R_Empty = My_rg.Find(vbNullString, After:=Cells(My_Row, Col))


If Not R_Empty Is Nothing Then
 R_Empty.Resize(, Col - R_Empty.Column + 1) = vbNullString
End If

End Sub
ABCDEFGH

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]47[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]Salim[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]Mrexcel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"]dsv[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: center"][/TD]

</tbody>
Sheet1​
 
Last edited:
Upvote 0
salim,

I am not sure if you noticed, but the question is an extension of the original question that was asked.
Your code is missing most of those details (i.e. your ranges don't reflect their descriptions, and the code misses the first part, which is verifying non-blank entries).
That is one of the reasons why our code looks so different (and all my codes are "Worksheet_Change" event procedures).
 
Upvote 0
Joe4 said:
salim,

I am not sure if you noticed, but the question is an extension of the original question that was asked.
Your code is missing most of those details (i.e. your ranges don't reflect their descriptions, and the code misses the first part, which is verifying non-blank entries).
That is one of the reasons why our code looks so different (and all my codes are "Worksheet_Change" event procedures).
Click to expand...

I gave you the file as an example
You can edit the code as desired
 
Upvote 0
I gave you the file as an example
You can edit the code as desired
Click to expand...
I didn't ask the question, I was the one who provided the original (and subsequent) answers!;)

I was just pointing out that it appears that your replies missed some important details.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA_change array to Range
2
Replies
19
Views
1K
SP321
S
S
  • Solved
Combining multiple worksheet change events
Replies
1
Views
511
SP321
S
N
  • Question Question
Slow vba with opening 60 files
Replies
7
Views
269
NickvdB
N
A
  • Solved
Workbook_BeforeClose() is not preventing user to close even when specific ranges are blank
Replies
4
Views
290
John_w
J
cpg84
  • Question Question
Merging and unmerging cells based on another cell's value
Replies
2
Views
293
cpg84
cpg84

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top