Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Go to cell or row based on user entered value

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •