Comparing Values in Range M

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

This is from an earlier post that may have been a little
confusing. Let me try again...

I have columns M and N. The initial action will be in
ColumnM. ColumnN looks at the values in ColumnM.

In ColumnM, Values can be added, deleted, or the order in
which the values are listed can be switched around. If
the values in ColumnM are switched around, I dont want
ColumnN to swicth the values to Mirror ColumnM. The only
way the values in ColumnN can be modified is either if a
new value is added to columnM or a value is deleted from
columnM.


Thanx

Todd
 
Here is one possible interpretation of what you describe:

Sub CompareNtoM()
Dim rngM As Range, rngN As Range
Dim cell As Range, i As Long
Dim res As Variant
Set rngM = Range(Cells(1, "M"), Cells(Rows.Count, "M").End(xlUp))
Set rngN = Range(Cells(1, "N"), Cells(Rows.Count, "N").End(xlUp))
For Each cell In rngM
res = Application.Match(cell, rngN, 0)
If IsError(res) Then
Set rngN = rngN.Resize(rngN.Rows.Count + 1)
rngN(rngN.Rows.Count).Value = cell.Value
End If
Next
Union(rngN, rngM).Select
For i = rngN(rngN.Rows.Count).Row To 1 Step -1
Set cell = Cells(i, "N")
res = Application.Match(cell, rngM, 0)
If IsError(res) Then
cell.Delete Shift:=xlShiftUp
End If
Next

End Sub
 
Back
Top