Use Range of Cells to find the same data in other Cells 2

[senators40]

Hi,

I would like to do the following

Column A has items like

A1 = Yes
A2 = No
A3 = Maybe
A4 = Probably
A5 = Always
with a number of other categories down the line

I have items in column B which could vary

B1 = Yes
B2 = Maybe
B3 = Probably

I would like to identify all instances where the items in column B are in column A with an identifier such as bolding the cell in column A

Sometimes the items in column B will change but column A will not change

Thanks in advance

Jeff

 

[Bong]

Consider:

Sub test()
    Set rLookfor = Range("a1", [a1].End(xlDown))
    Set rLookin = Range("b1", [b1].End(xlDown))
    For Each c In rLookfor.Cells
        f = rLookin.Find(c.Value)
        If Not f Is Nothing Then
            [red]<do what you will>[/red]
        End If
    Next
End Sub

This assumes you want to look for the values in column A among (in) the values in column B. It also assumes that the values in both columns are contiguous starting in row 1.

_________________
Bob Rashkin

 

[SkipVought]

Hi,

No VBA code required. Use Format > Conditional Formatting
[tt]
Formua is: =MATCH(A1,$B:$B,0)>0
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[senators40]

I wanted to thank you for your response. Unfortunately I got an error message (Runtime Error 424 - Object Required) when I tried to run the program you wrote, however I was able to use the code to rewrite it to do what I wanted it to do.

I have the code below

Sub test()

Dim rLookfor
Dim rLookin

Set rLookfor = Range("a1", [a1].End(xlDown))
Set rLookin = Range("b1", [b1].End(xlDown))


Do Until ActiveCell.Value = ""
For Each c In rLookfor.Cells
f = rLookin.Find(c.Value)

If ActiveCell.Value = f Then


Selection.Font.Bold = True

End If
Next

ActiveCell.Offset(1, 0).Select

Loop

End Sub

I also wanted to thank Skip for the response as well. I will use that for some other circumstances where I need to compare information.

Thank you for your response

Jeff