Finding a number and coloring the cell he is in

[Americanu22]

I am using excel 2003 (but an answer in 2007 will be good too because I will ask my company to instal me 2007) .For example: I have a row completed from A1 to D1 with numeric values. If I enter a value
in E1 that corresponds with the value from A1 (just an example it can be B1 or C1 or D1), how can I do so that A1 cell will be coloured and that excell will direct me to it?

 

[AlanAnderson]

Hi,

I am a relative newbie. The following routine could undoubtedly be done better but it does work. NB It allows for numbers not being unique

This routine assumes that the number you are looking for is entered into A1 and the column containing your list of numbers is from B1 downwards.

Regards,

Alan

Sub FindAndColor()
    Dim FinalRow As Long
    Dim wsA As Worksheet
    Dim x As Long
    Dim vTarget As Double
    Dim vSeek As Double
    
    Set wsA = Worksheets("Test")
    vSeek = Val(wsA.Cells(1, 1))
    FinalRow = wsA.Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            If vSeek = wsA.Cells(x, 2) Then
            Range("B" & x).Select
                With Selection.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                End With
            End If
        Next x
End Sub