Thread: Loop to test 2 matches using 4 variables

Hi guys,

I am trying to test for 2 matches based on 4 variables/columns (ie. B = C and D = E) and then colour the relevant row.

I felt I could use the same methodology from the code I developped to colour the row to a single match (B = C), but I am not having much success as such would be extremely grateful for some suggestions.

The original thread to match one variable is:
http://www.excelforum.com/excel-prog...in-a-loop.html

Adapted code with little success:

Code:

Sub Colour_filter_v2()

Dim Rng, rng1 As Range
Dim intcounter, intcounter1 As Double
Dim intNumberOfRecords, intNumberOfRecords1 As Double
Dim setdate, idate As Range
Dim intcounter2, intcounter3 As Double
Dim intNumberOfRecords2, intNumberOfRecords3 As Double
Dim a As Integer

Set Rng = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rng1 = ActiveSheet.Range("C2", Range("C" & Rows.Count).End(xlUp))
Set setdate = ActiveSheet.Range("D2", Range("D" & Rows.Count).End(xlUp))
Set idate = ActiveSheet.Range("E2", Range("E" & Rows.Count).End(xlUp))
    
intNumberOfRecords = Rng.Rows.Count - 1
intNumberOfRecords1 = rng1.Rows.Count - 1
intNumberOfRecords2 = setdate.Rows.Count - 1
intNumberOfRecords3 = idate.Rows.Count - 1
a = 0
    
    For intcounter = 0 To intNumberOfRecords
      For intcounter1 = 0 To intNumberOfRecords1
        For intcoutner2 = 0 To intNumberOfRecords2
          For intcounter3 = 0 To intNumberOfRecords3
            If (Rng(intcounter) = rng1(intcounter1)) And _
            (setdate(intcounter2) = idate(intcounter3)) Then
                Rows(intcounter1 + 1).Select
                Selection.Interior.color = vbGreen
                a = a + 1
            End If
           Next
        Next
      Next
    Next
        
MsgBox "Number of matches: " & a

End Sub

Your time is appreciated!

How about this

Code:

Sub Colour_filter_v2()

Dim Rng1 As Range
Dim Counter As Long

Set Rng1 = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
   
For Each Ccell In Rng1
  'If B=C and D=E Then
  If Ccell = Ccell.Offset(0, 1) And Ccell.Offset(0, 2) = Ccell.Offset(0, 3) Then
    Ccell.Resize(1, 4).Interior.Color = vbGreen
    Counter = Counter + 1
  End If
Next

MsgBox "Number of matches: " & Counter

End Sub

Hi rwgrietveld,

Unfortunately this code doesnt return me any results.

Perhaps should have mentioned that column B is ~2,000 rows whilst column C is ~65,000 rows (as are D and E respectively), which is why i felt i would require a nested loop.

I have attached an exmaple of what I am trying to accomplish. Hopefully this will do a better job explaining that I have done.


cross-post reference to other thread http://www.mrexcel.com/forum/showthr...28#post2163328

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!