Using conditional formatting in excel

New Contributor

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 



excel 1.png

Basically I would like to edit the QTY next to highlighted cell. 


All help would be highly appreciated 

6 Replies
Control+F opens the Find box which allows you to find the cell in question.


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.



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).



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
            Target.Interior.Color = vbRed
        End If
        Target.Interior.ColorIndex = xlNone
    End If
End If
End Sub





Thank you, for all your help




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.

Related Conversations
Need help with =IFerror function
Leon_Redclifft in Excel on
0 Replies
Rune91 in Excel on
0 Replies
Unable to open my exel worksheet
chrisngundu in Excel on
3 Replies
Weekly rotating list
Rashy_wearer2 in Excel on
1 Replies
Formula to calculate between "Closing Balances"
calof1 in Excel on
2 Replies
Userform-Transferred Data must not be editable
Mr_Buttons in Excel on
0 Replies