title: Comparing records from two columns with unequal lengths
body: <p>I am doing validation of two columns(with unequal lengths) present on two different sheets of excel.</p>
  
  <p>The first sheet name is 'Task'. Column 'A' of the 'Task' sheet has about 200 City Names. </p>
  
  <p>The second sheet is 'Cities'. Column 'A' of the 'Cities' sheet has about 8000+ City Names. </p>
  
  <p>Now I need to do validation such that the name of City in Column A of 'Task' sheet should be </p>
  
  <ol>
  <li><p>Either one among the City Names specified in column 'A' of sheet 'Cities' </p></li>
  <li><p>Or It can have multiple entries separated with semicolon; after separating all cities based on semicolon each city names shall match with that one in column D of 'Cities' sheet.</p></li>
  <li><p>If not among both the above cases then it should be '<strong>All</strong>'</p></li>
  </ol>
  
  <p>The cells in 'Task' Sheet from which City names do not match are to be turned in Red background</p>
  
  <p>My Code is as follows: (am just giving required part of code)</p>
  
  <pre><code>Dim CityString As String
  Dim CityArray() As String
  
  'Get the last row
  'Dim lastRow As Integer
  LastRow = ActiveSheet.UsedRange.Rows.Count
  
  
  Dim c As Range
  Dim d As Range
  Dim e As Variant
  
  'Turn screen updating off to speed up macro code.
  'User won't be able to see what the macro is doing, but it will run faster.
  Application.ScreenUpdating = False
  
  For Each c In Worksheets("Task").Range("A2:A" &amp; LastRow).Cells
  CityString = c
  CityArray() = Split(CityString, ";")
  For Each e In CityArray()
  e = Trim(e)
      For Each d In Worksheets("Cities").Range("A2:A" &amp; LastRow).Cells
          c.Interior.Color = vbRed
  
          If (UCase(e) = UCase(d) Or c = "All") Then
          c.Interior.Color = vbWhite
          Exit For
          End If
      Next
      If c.Interior.Color = vbRed Then
      Exit For
      End If
  Next
  Next
  </code></pre>
  
  <p>Now, the above code is valid only when both the sheets (Sheet1 - 'Task' and Sheet2 - 'Cities' have equal number of records. If Sheet2 - 'Cities' has more records than the 'Task' Sheet, the above code do not work.</p>
  
  <p>For Example: New York is at 'A55' Cell of 'Task' sheet.
  It's also there in 'Cities' Sheet at 'A41'. My code validates the cell correctly.</p>
  
  <p>For Example: 'A53' of 'Task' sheet I have 'Tokyo' and 'Task' Sheet has only 200 records, where as 'Tokyo' is present at 'A988' of 'Cities' sheet, which has some 8000+ records, then my code does not validate this cell correctly.</p>
  
  <p>Can anyone give me an alternative code for the same?
  I need code to compare records from two columns with unequal lengths.</p>