09-09-2019 05:46 AM
09-09-2019 05:46 AM
I am currently using a conditional formatting for searching for a word or phrase. I currently have it set so that it highlights the cell that contains the word. I would rather have it either select the cell that contains the word or move to the cell that is next to it as that is the cell that I would like to edit . Is there a feature that allows me to do this
Basically I would like to edit the QTY next to highlighted cell.
All help would be highly appreciated
09-09-2019 06:11 AM
Conditional formatting cannot select any cell, it applies only some cosmetic effects on the cells based on the rule you set for conditional formatting.
What you are trying to achieve is possible with the help of VBA. If you are open for a VBA solution, please elaborate your requirement a bit and upload a sample workbook.
09-09-2019 06:25 AM
I would like to enter the keyword in A1 and it should then automatically take me to the cell that is next to the one with the Keyword(if it is able to take me to the cell with the keyword that would be sufficient).
09-09-2019 07:17 AMSolution
Please find the attached with a Sheet Change Event which is placed on Sheet1 Module.
To view the code, right click on Sheet1 Tab --> View Code.
As per the code, once you input a barcode in A1, the code will look for the barcode in column C and if it finds one, it will select the corresponding cell in column D and highlight the Cell A1 with Yellow color to indicate that a search was found. If a barcode you entered in A1 is not found in column C, the A1 will turn into the red color.
Let me know if this is what you were trying to achieve.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim Rng As Range If Target.Address(0, 0) = "A1" Then If Target <> "" Then Set Rng = Range("C:C").Find(what:=Target.Value, lookat:=xlWhole) If Not Rng Is Nothing Then Rng.Offset(0, 1).Select Target.Interior.Color = vbYellow Else Target.Interior.Color = vbRed End If Else Target.Interior.ColorIndex = xlNone End If End If End Sub
09-12-2019 11:08 PM
You're welcome! Glad I could help.
If that takes care of your original question, please accept the proposed solution as Best Answer/Response which will mark your question as Solved.
Also, you may click the Like button underneath a post, that's another way to say thanks.