Avatar of Lawrence Salvucci
Lawrence Salvucci
Flag for United States of America asked on

Highlight & Go To Cell based on criteria

I need to be able to highlight certain cells based on 3 criteria entries in cells B3, C3, & D3.
The criteria is as follows:

B3 = Grade Type. This is located at the top of each section in cells K4 & Q4.
C3 = Diameter. This is located in columns H & N. It is only shown in 1 cell for each section. You'll understand what I mean when you open the attached sample file.
D3 = Ftg. This should find the ftg. that either equals what is in this cell or the next ftg. that is greater than the footage in this cell.

Right now I have 2 material sections but I could add more columns for other material sections or more rows for more sizes.

So how this will work is when someone enters the info in those 3 criteria cells I want to highlight the cell that shows the $/LB & $/FT and then jump to those cells and select them so the user knows where it is. The cells should be highlighted in the color I used in the sample file attached and then are selected as shown as well.
Sample-File---Lookup.xlsx
Microsoft Excel

Avatar of undefined
Last Comment
gowflow

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lawrence Salvucci

ASKER
Hi,
That works great! Thank you! But is there a way to have the cursor also go to the highlighted cells too so the user will know where it's located since there could be lots of rows to scroll through? I would want it jump down to that selection and have it show it by putting the cursor on those cells.
SOLUTION
gowflow

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gowflow

Here you go you also have a vba solution. Did not see ssaqibh answer before posting.
gowflow
Saqib Husain

Sorry, I missed the "jump to those cells" part.

Here is my way of doing this
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Long
    If Not Intersect(Target, Range("B3:D3")) Is Nothing Then
        col = Application.Match(Range("B3"), Range("4:4"), 0)
        blk = Application.Match(Range("C3"), Cells(1, col).EntireColumn.Offset(, -3), 0)
        rw = Application.Match(Range("D3"), Range(Cells(blk, col), Cells(blk, col).End(xlDown).Offset(-1)).Offset(, -1), 1)
        Cells(rw + blk, col).Resize(, 2).Select
    End If
End Sub

Open in new window


But I personally feel that it is not a good idea to make it part of a worksheet_change. This can turn out to be a nuisance especially if any of the parameters are wrongly entered by mistake. Instead I would recommend using a button which fires off the macro once the desired data is entered.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Lawrence Salvucci

ASKER
Would it be possible to walk me through this code? I might be changing the rows & column locations and I just want to know how this will impact it and what I need to update in this code as well.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Long
    If Not Intersect(Target, Range("B3:D3")) Is Nothing Then
        col = Application.Match(Range("B3"), Range("4:4"), 0)
        blk = Application.Match(Range("C3"), Cells(1, col).EntireColumn.Offset(, -3), 0)
        rw = Application.Match(Range("D3"), Range(Cells(blk, col), Cells(blk, col).End(xlDown).Offset(-1)).Offset(, -1), 1)
        Cells(rw + blk, col).Resize(, 2).Select
    End If
End Sub

Open in new window

Lawrence Salvucci

ASKER
Hello...I figured out how your code ticks after reading it a few times. My question is how do I have this code fire from a button instead of having it on the worksheet_change event? I created 2 buttons on my sheet, 1 for firing this code and the other for clearing the results so the user can start over. Just need to know how to set this up under a button. I tried just copying the data to a command button's code but I keep getting an "Object Required" error.
Saqib Husain

Try this code for the button. I have made a few other changes

    Dim col As Long, blk As Long, rw As Long
    Dim ws As Worksheet
    Set ws = Sheets("Mat'l")
    On Error GoTo xit
    col = Application.Match(ws.Range("B3"), ws.Range("4:4"), 0)
    blk = Application.Match(ws.Range("C3"), ws.Cells(1, col).EntireColumn.Offset(, -3), 0)
    rw = Application.Match(ws.Range("D3") - 0.00001, ws.Range(Cells(blk, col), Cells(blk, col).End(xlDown).Offset(-1)).Offset(, -1), 1)
    ws.Cells(rw + blk, col).Resize(, 2).Select
xit:
    If col = 0 Then ws.Range("B3").Select: Exit Sub
    If blk = 0 Then ws.Range("C3").Select: Exit Sub
    If rw = 0 Then Resume Next

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gowflow

did you try the solution I posted?
gowflow
Lawrence Salvucci

ASKER
I did and both work flawless. I do need to award ssaqibh with the majority of the points though since he did answer first and I am using his solution. Although I will keep yours as well as a backup. Thank you both for posting your solutions. I appreciate the help from both of you!
gowflow

Tks ur comments and glad we could help.

Just a small note for your future expansions in sections if you decide to add sections as you first mentioned in your initial post, my solution would work without changing anything provided you respect the same layout ie

you keep the section 5 columns where the first one is the size and the last 2 are the ones you need to input and also your cell 303 PDB Cell K4 will need to be merged like you have it now with K5. So in my macro as long as you respect this layout you have not to worry about changing anything in the code it will work just fine.

Just felt this clarification important if and when you need to use this version.
Regards
gowflow
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck