Comparing corresponding cells between multiple named ranges

Assuming you have named ranges "myrange1", "myrange2", and "myrange3" and they are all the same size..

Sub RangeCompare()
    
    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
    
    Set r1 = Range(ThisWorkbook.Names("myrange1").RefersTo)
    Set r2 = Range(ThisWorkbook.Names("myrange2").RefersTo)
    Set r3 = Range(ThisWorkbook.Names("myrange3").RefersTo)
    
    MsgBox "Matching count is " & CountMatch(r1, r2, r3)
    
End Sub

Function CountMatch(r1 As Range, r2 As Range, r3 As Range) As Integer
    
    Dim i As Integer
    Dim n As Integer
    
    n = r1.Cells.Count
    For i = 1 To n
        If r1.Cells(i) = "yes" And r2.Cells(i) = "no" And r3.Cells(i) = "yes" Then CountMatch = CountMatch + 1
    Next i
    
End Function

Regards, AB

Thanks for the response AB. I have tried incrementing using the code highlighted below. Unless I'm missing something, that code increments the cell down by one row. My problem is my named ranges are non-contiguous cells and the row offset to the next cell varies. That is why I am using the named range. The "for each" loop will increment my named ranges properly, but it only operates on one range at a time (AFAIK). I need to increment the cells from three named ranges within the same "for" loop. Any ideas on how the "next" operator on the "for each" loop works?

    n = r1.Cells.Count
    For i = 1 To n
        If r1.Cells(i) = "yes" And r2.Cells(i) = "no" And r3.Cells(i) = "yes" Then CountMatch = CountMatch + 1
    Next i

The r1.cells(i) construct works through a range "across and down". If r1 is, say, A1:C3 and r2 is F4:H6 then

r1.cells(1) = A1 and r2.cells(1) = F4
r1.cells(2) = B1 and r2.cells(2) = G4
r1.cells(3) = C1 and r2.cells(3) = H4
r1.cells(4) = A2 and r2.cells(4) = F5
r1.cells(5) = B2 and r2.cells(5) = G5
r1.cells(6) = C2 and r2.cells(6) = H5
r1.cells(7) = A3 and r2.cells(7) = F6
r1.cells(8) = B3 and r2.cells(8) = G6
r1.cells(9) = C3 and r2.cells(9) = H6

Regards, AB

My problem is that r1 is more like "A1, A9, A15, A22" instead of A1:A22. My other ranges are similarly non-contiguous. Even so the "for each" loop increments the non-contiguous named range properly. I'm looking for the method that the "next" operator in the "for each" loop uses to increment to the next cell.

Ah, OK. I had assumed non-contiguous blocks with the blocks themselves being contiguous.
This modified version taking account of range areas may do the trick for you.

Sub RangeCompare()
    
    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
    
    Set r1 = Range(ThisWorkbook.Names("myrange1").RefersTo)
    Set r2 = Range(ThisWorkbook.Names("myrange2").RefersTo)
    Set r3 = Range(ThisWorkbook.Names("myrange3").RefersTo)
    
    MsgBox "Matching count is " & CountMatch(r1, r2, r3)
    
End Sub

Function CountMatch(r1 As Range, r2 As Range, r3 As Range) As Integer
    
    Dim i As Integer
    Dim j As Integer
    
    For i = 1 To r1.Areas.Count
        For j = 1 To r1.Areas(i).Cells.Count
            If r1.Areas(i).Cells(j) = "yes" _
            And r2.Areas(i).Cells(j) = "no" _
            And r3.Areas(i).Cells(j) = "yes" _
            Then CountMatch = CountMatch + 1
        Next j
    Next i
    
End Function

Regards, AB

Thanks AB. The areas are what I was missing. Great job!