Find and Color Macro

[rcholst2]

I need to select one variable in Column A Row 1 and check to see if it exists in Column B and then Makr it red if it does.

There are over 1000 variables in Column A that need to be found in Column B and marked.

I started doing this by hand with copying and pasing in the Find command and then coloring... after 50 my hand got tired....

Any suggestions for a Macro?

 

[cfree36]

You can put a vlookup formula in conditional formatting.

Highlight your range in column A

Goto Format / Conditional Formatting

Change Cell Value Is to Formula Is

Enter
=VLOOKUP($A1,$B$1:$B$16,1,0)

Changing the range to fit your needs.

Choose the format to be red.

 

[Von Pookie]

Select the data in column A, then select conditional formatting from the Format menu.

Enter a condition of

Formula is | =COUNTIF($B:$B,$A1)>=1

and click the format button to set the colors.

When you click OK it should highlight any cell in column A that is listed in column B.

 

[hokiegirl]

Macro idea

For a macro, here's what I would do:

Sub FindDuplicates()

'establish starting row numbers
rownumA=1
rownumB=1


Do While Cells(rownumA,1).value <>""

  Do While Cells(rownumB,1).Value <> ""
	If Cells(rownumA,1).Value = Cells(rownumB,2).Value Then
		Cells(rownumA,1).Font.ColorIndex = 3
		Exit Do
	Else
		rownumB=rownumB+1
	End If
  Loop

rownumA=rownumA+1
rownumB=1

Loop

End Sub

 

[Joe Was]

This pair of Subs should be quite fast!


Sub myFindMatch()
'Run from standard module, like: Module1.
'Find and highlight each cell in Column: B, that matches a value in Column: A
Dim myRngLst As Range, myRngDat As Range, myFunct As Object

Set myFunct = Application.WorksheetFunction
Set myRngDat = Sheets("Sheet1").Range(Cells(1, 2), Cells(Range("B65536").End(xlUp).Row, 2))
Set myRngLst = Sheets("Sheet1").Range(Cells(1, 1), Cells(Range("A65536").End(xlUp).Row, 1))

For Each Cell In myRngDat
If (Cell.Value <> "" And myFunct.CountIf(myRngLst, Cell.Value) <> 0) Then
Cell.Interior.ColorIndex = 38
End If
Next Cell
End Sub


Sub myReSetColor()
'Run from standard module, like: Module1.
'Re-Set Column: B cell colors!
Dim myRngDat As Range

Set myRngDat = Sheets("Sheet1").Range(Cells(1, 2), Cells(Range("B65536").End(xlUp).Row, 2))
myRngDat.Interior.ColorIndex = xlNone
End Sub