Beginner problem

JAndreassen

I need a macro in Excel that starts in for instance cell A1 and then checks the whole B-column if the same value is present there, if not continue to A2 and check whole B-column again.

I've figured I may use For/next, but I'm kinda stuck.

Thanks for help.

Andy Pope

Hi,

We need a bit more detail.

What happens when you find a match in column B for the value in A1
Is it possible that more that 1 cell in column B can match and if so how should this be handled
What sort of data is in the columns Numbers, dates, text.
When should you stop checking the values in column A

JAndreassen

Hello.

- when the same value is found the value in column B can be marked red for instance, but that I can figure out

- it's possible that more than one column i B match column A, but the same should be done, so the loop has to run until the end of column B

- type of data will either be numbers or text

- it should stop when there no longer is no value in A, for instance countA(A1:A16000)

Andy Pope

Try this on a copy of your data.

Sub X()

Dim rngFind As Range
Dim rngCell As Range
Dim strFirstAddress As String

With ActiveSheet
For Each rngCell In .Range("A1", .Range("A1").End(xlDown)).Cells
With .Range("B:B")
Set rngFind = .Find(rngCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.Interior.ColorIndex = 3
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
Set rngFind = Nothing
End If
End With
Next
End With

End Sub

Jørgen

Thank you very much, this did the trick. Now I only need to sit down and understand how this sub works Smile

Beginner problem

Re: Beginner problem

Re: Beginner problem

Re: Beginner problem

Re: Beginner problem