Formatting a cell during a search

[Cubicle_51]

Hello everyone,

Here is my issue, I have column of data that I'm scanning using the find feature to see if the number I scanned has a match in the column.

Currently, I'm highlighting the cell green manually if it is a match. I've searched the forum and tried multiple ways in conditional formatting
but have been unable to produce the results I'm looking for

what I'm looking for is a way to scan my number. if it's a match, fill the matching cell in the column green and stay green when I scan another number. so at the end I have all matching numbers cell filled green and any non matches will remain uncolored


while I'm sure it's a simple thing....I'm unable to find the solution

Thanks in advance

 

[DanteAmor]

Try this

Sub finder()
    Set f = ActiveSheet.Range("A:A").Find(2, LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        f.Select
        f.Interior.ColorIndex = 4
    End If
End Sub

If you have difficulties, put your code here and I'll review it.

 

[Cubicle_51]

I wanted to give you a quick update.
I was out sick for a a few days and then when I came back to work, the dept that in charge of the report coded a search and highlight feature into a window. so while I thank you for the help...they beat me to the punch and did the work for me on the back end.

Thanks again

 

[DanteAmor]

Hope all is well.
I'm glad to help you. Thanks for the feedback.

 

[stephen_w_chan]

Hi, I am trying to do the same thing. Do you have any suggestions? Here is a small sample of what I'm trying to do.

Column A contains serial numbers anywhere from 1 to 1000 rows.
Cell C1 contains the value I want to search which is inputted from a bar scanner
Same as original post where I'm looking for is a way to scan my number. If it's a match, fill the matching cell in the column A green and stay green when I scan another number. So at the end I have all matching numbers cell filled green and any non matches will remain uncolored

Thanks

 

[DanteAmor]

Column A contains serial numbers anywhere from 1 to 1000 rows.
Cell C1 contains the value I want to search which is inputted from a bar scanner

Put the following code in the events of your sheet and it will work automatically, every time you update cell C1, the cell in column A containing that number will be highlighted in green.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ch As Range, f As Range
  Set ch = Intersect(Target, Range("C1"))
  If Not ch Is Nothing Then
    If ch.CountLarge > 1 Then Exit Sub
    If ch.Value = "" Then Exit Sub
    Set f = Range("A:A").Find(ch.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      f.Interior.Color = vbGreen
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[stephen_w_chan]

That works great! Exactly what I needed. Thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.