"if else" in a "for each" loop

Joschu

New Member
Joined
Oct 9, 2014
Messages
2
The goal of this macro is to compare two lists (columns) A and B (A>B). For each value that in B that also exists in A I want an "x" offset from the value in A. So far so good.
For each value in B that does not exist in A, I want the value to be marked red in B. I don't know how to get the last part to work. Can you help me please? Thank you.

Code:
Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("F1:F100")
   
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then y.Offset(0, 10) = "x" Else x.Interior.ColorIndex = 3
            Next y
    Next x
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("F1:F100")
   
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then
                y.Offset(0, 10) = "x"
            Else
                x.Interior.ColorIndex = 3
            End If
        Next y
    Next x
End Sub
 

Joschu

New Member
Joined
Oct 9, 2014
Messages
2
Thank you for the fast response. I tried that before, but this marks all cells in list B red. I want only the ones that don't also exist in list A.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,157
Messages
5,527,131
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top