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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here you go you also have a vba solution. Did not see ssaqibh answer before posting.
gowflow
gowflow
Sorry, I missed the "jump to those cells" part.
Here is my way of doing this
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.
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 SubBut 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.
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 SubASKER
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.
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
did you try the solution I posted?
gowflow
gowflow
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!
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
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
ASKER
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.