macro to search in a specific column

[pinmaster]

Hi, can anyone one modifie this macro to search only in a specifi
column, in this case column B1....

Cells.Find(What:=Range("A2").Value, After:=ActiveCell
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns
SearchDirection:=xlNext, _
MatchCase:=True).Activate

and would it be possible to change the font color to "red" to make i
easier to see which cell is highlighted?

Thanks
Jean-Guy Collette
Canad

 

[JE McGimpsey]

One way:

Dim found As Range
Set found = Columns(2).Find( _
What:=Range("A2").Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not found Is Nothing Then
found.Activate
found.Font.ColorIndex = 3
End If

 

[pinmaster]

Hi...there's a few problems with the macro, first it doesn't find th
next entry when I click the "find" button again, and second the tex
stays red after making another search, I would like it to return to th
original font color if possible when I make another search!

Jean-Gu

 

[JE McGimpsey]

Sounds more like a problem with your original specification, but easily
added:

Public Sub test1()
Static rFound As Range
Static nOriginalColor
If Not rFound Is Nothing Then _
rFound.Font.ColorIndex = nOriginalColor
Set rFound = Columns(2).Find( _
After:=Cells(ActiveCell.Row, 2), _
What:=Range("A2").Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rFound Is Nothing Then
rFound.Activate
nOriginalColor = rFound.Font.ColorIndex
rFound.Font.Color = RGB(255, 0, 0)
End If
End Sub

 

[pinmaster]

Percect....thanks, and next time I will try to be more specific

Jean-Guy Collette
Canada

 

[JE McGimpsey]

Percect....thanks, and next time I will try to be more specific

Note that if you save your workbook with one of the cells highlighted
with red, when you reopen your workbook, that cell will remain red.

If you want a general routine to highlight cells, see Chip Pearson's
RowLiner:

http://cpearson.com/excel/RowLiner.htm

or

http://www.mcgimpsey.com/excel/highlightrow.html