SOLVED
Home

Using conditional formatting in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-843861%22%20slang%3D%22en-US%22%3EUsing%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843861%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20currently%20using%20a%20conditional%20formatting%20for%20searching%20for%20a%20word%20or%20phrase.%20I%20currently%20have%20it%20set%20so%20that%20it%20highlights%20the%20cell%20that%20contains%20the%20word.%20I%20would%20rather%20have%20it%20either%20select%20the%20cell%20that%20contains%20the%20word%20or%20move%20to%20the%20cell%20that%20is%20next%20to%20it%20as%20that%20is%20the%20cell%20that%20I%20would%20like%20to%20edit%20.%20Is%20there%20a%20feature%20that%20allows%20me%20to%20do%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20358px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130715i9DD7E0F1928305F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel%201.png%22%20title%3D%22excel%201.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBasically%20I%20would%20like%20to%20edit%20the%20QTY%20next%20to%20highlighted%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20help%20would%20be%20highly%20appreciated%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-843861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843869%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843869%22%20slang%3D%22en-US%22%3EControl%2BF%20opens%20the%20Find%20box%20which%20allows%20you%20to%20find%20the%20cell%20in%20question.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843901%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405838%22%20target%3D%22_blank%22%3E%40Gangat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConditional%20formatting%20cannot%20select%20any%20cell%2C%20it%20applies%20only%20some%20cosmetic%20effects%20on%20the%20cells%20based%20on%20the%20rule%20you%20set%20for%20conditional%20formatting.%3C%2FP%3E%3CP%3EWhat%20you%20are%20trying%20to%20achieve%20is%20possible%20with%20the%20help%20of%20VBA.%20If%20you%20are%20open%20for%20a%20VBA%20solution%2C%20please%20elaborate%20your%20requirement%20a%20bit%20and%20upload%20a%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843930%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843930%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20enter%20the%20keyword%20in%20A1%20and%20it%20should%20then%20automatically%20take%20me%20to%20the%20cell%20that%20is%20next%20to%20the%20one%20with%20the%20Keyword(if%20it%20is%20able%20to%20take%20me%20to%20the%20cell%20with%20the%20keyword%20that%20would%20be%20sufficient).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-844030%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-844030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405838%22%20target%3D%22_blank%22%3E%40Gangat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20with%20a%20Sheet%20Change%20Event%20which%20is%20placed%20on%20Sheet1%20Module.%3C%2FP%3E%3CP%3ETo%20view%20the%20code%2C%20right%20click%20on%20Sheet1%20Tab%20--%26gt%3B%20View%20Code.%3C%2FP%3E%3CP%3EAs%20per%20the%20code%2C%20once%20you%20input%20a%20barcode%20in%20A1%2C%20the%20code%20will%20look%20for%20the%20barcode%20in%20column%20C%20and%20if%20it%20finds%20one%2C%20it%20will%20select%20the%20corresponding%20cell%20in%20column%20D%20and%20highlight%20the%20Cell%20A1%20with%20Yellow%20color%20to%20indicate%20that%20a%20search%20was%20found.%20If%20a%20barcode%20you%20entered%20in%20A1%20is%20not%20found%20in%20column%20C%2C%20the%20A1%20will%20turn%20into%20the%20red%20color.%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20this%20is%20what%20you%20were%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0AIf%20Target.CountLarge%20%26gt%3B%201%20Then%20Exit%20Sub%0ADim%20Rng%20As%20Range%0AIf%20Target.Address(0%2C%200)%20%3D%20%22A1%22%20Then%0A%20%20%20%20If%20Target%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Set%20Rng%20%3D%20Range(%22C%3AC%22).Find(what%3A%3DTarget.Value%2C%20lookat%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20If%20Not%20Rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Rng.Offset(0%2C%201).Select%0A%20%20%20%20%20%20%20%20%20%20%20%20Target.Interior.Color%20%3D%20vbYellow%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20Target.Interior.Color%20%3D%20vbRed%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Target.Interior.ColorIndex%20%3D%20xlNone%0A%20%20%20%20End%20If%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853865%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853865%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20for%20all%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853883%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20conditional%20formatting%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405838%22%20target%3D%22_blank%22%3E%40Gangat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20I%20could%20help.%3C%2FP%3E%3CP%3EIf%20that%20takes%20care%20of%20your%20original%20question%2C%20please%20accept%20the%20proposed%20solution%20as%20Best%20Answer%2FResponse%20which%20will%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3CP%3EAlso%2C%20you%20may%20click%20the%20Like%20button%20underneath%20a%20post%2C%20that's%20another%20way%20to%20say%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gangat
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.

@Gangat 

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.

Highlighted

@Subodh_Tiwari_sktneer 

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

Solution

@Gangat 

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

 

 

@Subodh_Tiwari_sktneer 

Thank you, for all your help

 

@Gangat 

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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies