Go to cell or row based on user entered value

[prometheus]

I have a worksheet which contains information in rows based on a patients weight. The range of weight is entered in column A (A3:A100). I would like to be able to have the user input a patient's weight in a cell (say A2) and then have the speadsheet scroll automatically to that corresponding row and then highlight just that row (or all rows except the desired row. Thanks in advance!

 

[Joe Was]

Right click the sheet tab lable, select "View Code" then paste a copy of the code below to the sheet module. JSW

Sub myColor()
'By Joe Was, 2/2002.
'This code will find the cell(s) colored by the myWeight sub below.
Dim myRowNum As Long
myRowNum = ActiveSheet.UsedRange.Rows.Count
Selection.Select
Do Until Selection.Row = myRowNum + 1
If Selection.Interior.ColorIndex<> xlNone Then
'To select entire row, un-comment below!
'Selection.EntireRow.Select
GoTo mySelect
Else
Selection.Offset(1, 0).Select
End If
Loop
End
mySelect:

End Sub


Sub myWeight()
'By Joe Was, 2/2002.
'This code will find the value entered in A2 on sheet1.
'Change "Worksheets( ) to your sheet number below.
Application.ScreenUpdating = False
With Worksheets(1).Range("A3:A100")
Set c = .Find(Worksheets(1).Range("A2"), LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address<> firstAddress
End If
End With

Application.ScreenUpdating = True
End Sub

You will run this code with a form button or hot-key option. Tools, Macro, Macros, Options then add your hot key letter. The sub's will find and color all the cells that match the find value, the myColor sub can be re-run to find the next colored cell.

You could add code to reset the colored cells to noColor or just don't save the workbook after the cells have been colored?
JSW
This message was edited by Joe Was on 2002-03-01 14:44

 

[prometheus]

Joe, Thanks for the "my weight" and "my color" macros. If you recall I had a series of patient weights in column A and needed to highlight and scroll to the row with the user entered weight. The selected cell was colored yellow by you macro. Unfortunately, the macros did not scroll the sheet automatically to the selected (colored) row. I'd appreciate if you could try to fix the macros so that the worksheet will scroll automatically to the selected (colored) cell. I appreciate your help. I've always accomplished what I wanted to do with macros in the past and I am not proficient with writing visual basic code. The weights in the worksheet are now in column A range A5:A100. I am now entering the desired weight in cell F2 instead of A2. Thanks for your time!