macro to search in a specific column

  • Thread starter Thread starter pinmaster
  • Start date Start date
P

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
 
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
 
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
 
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
 
Percect....thanks, and next time I will try to be more specific :-)

Jean-Guy Collette
Canada
 
Back
Top