+ Reply to Thread
Results 1 to 11 of 11

Formula or VBA code required to select cell based on another cell value

  1. #1
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Formula or VBA code required to select cell based on another cell value

    Hi,

    I have attached a test file to help get me the answer but in essence i would like to be able to get the worksheet to jump to a certain cell based on the value I enter into the cell D3 on the attached sheet. I'm assuming i would need some VBA code and maybe a button for this but I am fairly new to creating sheets and this isn't something I've had any requirement for in the past.

    thanks for taking the time to read.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: Formula or VBA code required to select cell based on another cell value

    Maybe this is the macro you are after. Since it uses event WorkSheet_Change to trigger it must be pasted in the sheet's module.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Formula or VBA code required to select cell based on another cell value

    Thanks for the quick response, I did come across an issue though, once I updated the target ranges I came up with the issue below

    Attachment 789349

  4. #4
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Formula or VBA code required to select cell based on another cell value

    I can't see your attachment but I can guess what the issue is.

    When you updated your range in your data validation, did you update the range(s) in the code provided?

    If you make "A6:A64" (in BOTH places) "A6:A999", you shouldn't need to update the code every time you make an adjustment to the data validation range.

  5. #5
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: Formula or VBA code required to select cell based on another cell value

    As @Croweater has already pointed out, if your range is dynamic the macro must also be made so.

  6. #6
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Formula or VBA code required to select cell based on another cell value

    Hi Guys,

    managed to get the code working good in my project when I followed the exact same principles of the test file I sent over. Would you mind helping me out with some final advice.

    what do each of the variables in the code control, I'm hoping a brief explanation would help when I integrate this code in other areas.

    - Is there a way that the cell can be selected so the workbook moves and displays the selected cell (the boldened result cell). I don't necessarily need the text to change its format just be selected to save scrolling up and down the page. in my layman language it would be

    1. when I select a number or value in this cell (validation cell)

    2. look up values in the specified range (adjustable or elongated as you pointed out)

    3. select matching cell (so that the worksheet focuses on the selected cell)

    also moving forward:

    - If I wanted to change my validation list and range of results to a set of text strings what changes would I need to make to the code for it to work in the same way.

    thanks for your help guys and apologies for the Noob questions!
    Attached Files Attached Files

  7. #7
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Formula or VBA code required to select cell based on another cell value

    Select the cell by lmaking this change/addition
    Please Login or Register  to view this content.
    It should work with text as well as numbers.

    Please Login or Register  to view this content.
    This bit of code sets all of the cells to a 'standard' font ( so as to make sure you only get the current selection bolded and increased in size
    Please Login or Register  to view this content.
    This bit of code finds the target.value (what you selected in D3) in the given range. (.value is the default but you can also look at .Row, .Address etc)
    Please Login or Register  to view this content.
    And the next bit of code makes that cell bigger and bolder!
    Last edited by Croweater; 07-29-2022 at 08:43 AM.

  8. #8
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: Formula or VBA code required to select cell based on another cell value

    I have updated the macro to move the focus to the picked cell (missed this in the previous version). You can choose if font size change and bold are needed, just activate the lines of code in the macro (before and after selection).
    I also added a macro that automatically creates the list without empty cells for the validation list in J2. To apply the update to the validation list you need to change sheet and back since it uses event WorkSheet_Activate to trigger.
    Sorry, unable to add code !!!
    Last edited by rollis13; 07-29-2022 at 09:38 AM.

  9. #9
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: Formula or VBA code required to select cell based on another cell value

    Please Login or Register  to view this content.
    End Sub

  10. #10
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Formula or VBA code required to select cell based on another cell value

    Evening, me again!

    started to write this message as another cry for help but ended up getting it sorted! Thanks, so pleased with this massive timesaver!

    My numbers between F62 down were all created by formula and I had to change these to raw data (type each number in each location) this finally sorted the issue.

    Only question I would ask is why this happened and is there a way that formula generated results can be read as I'm sure this will cause problems when it comes to changing this very to text strings formed by VLOOKUP.

    if I don't get an answer then id just like to say thanks again. this was really helpful and whilst I don't fully understand all the code, I have picked up a few nuggets that have pushed me along (a little!!)

  11. #11
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: Formula or VBA code required to select cell based on another cell value

    If you have formulas in column F this line of code should be with parameter LookIn:=xlValues, like this:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code to Select Cell based on multiple criteria
    By ankamshetti.nagaraj in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-02-2020, 01:28 AM
  2. [SOLVED] VBA code required to replace cell content of a cell based on the value in another cell
    By saugatapalit in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-10-2020, 05:26 AM
  3. [SOLVED] Code to goto and select cell based on value in another cell.
    By ericv101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2014, 08:01 PM
  4. [SOLVED] Highlight cell based on selected cell - Code required
    By SharpL in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-24-2014, 09:48 AM
  5. [SOLVED] Auto Filtering based on Cell Value? Code Required.
    By SharpL in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-22-2014, 04:57 AM
  6. Auto Filtering based on Cell Value? Code Required.
    By SharpL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-19-2014, 10:01 AM
  7. Replies: 3
    Last Post: 09-08-2014, 10:36 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1