Sep 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
Sep 09 2019 05:54 AM
Sep 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.
Sep 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).
Sep 09 2019 07:17 AM
SolutionPlease 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
Sep 12 2019 10:48 PM
Sep 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.
Sep 09 2019 07:17 AM
SolutionPlease 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