Highlight active cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1569545%22%20slang%3D%22en-US%22%3EHighlight%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569545%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20anybody%20know%20how%20to%20highlight%20the%20active%20cell%20selection%20in%20Excel.%20I%20have%20a%20large%20table%20of%20data%20in%20which%20recurring%20values%20are%20often%20searched%20for%20and%20whilst%20the%20search%20is%20accurate%20enough%2C%20it's%20still%20hard%20to%20see%20where%20the%20value%20has%20been%20located.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1569545%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569554%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569554%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20provide%20screenshot%3CBR%20%2F%3Ewhile%20search%20process%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569576%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F58192%22%20target%3D%22_blank%22%3E%40Malcolm%20McMaster%3C%2FA%3E%26nbsp%3B%20I%20get%20what%20you%20are%20saying.%26nbsp%3B%20Here%20are%20a%20few%20tips%20that%20might%20help%20you%20if%20you%20don't%20know%20them%20already%3A%3C%2FP%3E%3CP%3E1)%20to%20the%20left%20of%20the%20formula%20bar%20(where%20you%20type%20values%20in)%20is%20a%20box%20that%20shows%20the%20location%20of%20the%20current%20selection.%26nbsp%3B%20That%20will%20help%20you%20know%20where%20to%20look%3C%2FP%3E%3CP%3E2)%20the%20row%20and%20column%20headers%20for%20the%20selected%20cell(s)%20are%20slightly%20darker%20so%20you%20can%20zoom%20in%20on%20the%20right%20location%3C%2FP%3E%3CP%3E3)%20you%20can%20always%20click%20the%20FILL%20button%20to%20make%20the%20cell(s)%20yellow%20(or%20whatever%20color%20you%20choose)%20and%20then%20click%20it%20again%20and%20select%20to%20remove%20that%20fill%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569755%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F58192%22%20target%3D%22_blank%22%3E%40Malcolm%20McMaster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20you%20could%20add%20a%20macro%20to%20your%20worksheet%20containing%20the%20table%20to%20highlight%20the%20selected%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20also%20suggest%20adding%20a%20cell%20to%20your%20worksheet%2C%20which%20I%20named%20%22EnableHighlight%2C%22%20so%20you%20could%20turn%20it%20on%2Foff%20without%20having%26nbsp%3B%20to%20edit%20the%20vba%20code%20every%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20example%20file%20you%20can%20play%20with.%20If%20you%20right%20click%20on%20the%20worksheet%20tab%20and%20select%20%22view%20code%2C%22%20then%20you%20will%20see%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20do%20add%20the%20macro%20to%20your%20own%20workbook%2C%20then%20be%20sure%20to%20save%20it%20as%20.xlsm%20(macro%20enabled)%20and%20not%20a%20.xlsx%20or%20the%20macro%20will%20get%20stripped%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1569779%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1569779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%20Many%20thanks%20this%20is%20great%20start.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570215%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20active%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F58192%22%20target%3D%22_blank%22%3E%40Malcolm%20McMaster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20solely%20checking%20for%20duplicates%2C%20why%20not%20use%20conditional%20formatting%20to%20find%20and%20highlight%20in%20one%20easy%20step%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20data%20range%20to%20be%20checked%20then%2C%20from%20the%20Home%20tab%2C%20select%20Conditional%20Formatting%2C%20from%20the%20drop-down%20choose%20Highlight%20Cells%20Rules%20and%20then%20Duplicate%20Values.%26nbsp%3B%20Done!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Does anybody know how to highlight the active cell selection in Excel. I have a large table of data in which recurring values are often searched for and whilst the search is accurate enough, it's still hard to see where the value has been located.

 

5 Replies
Highlighted
Is it possible to provide screenshot
while search process ?
Highlighted

@Malcolm McMaster  I get what you are saying.  Here are a few tips that might help you if you don't know them already:

1) to the left of the formula bar (where you type values in) is a box that shows the location of the current selection.  That will help you know where to look

2) the row and column headers for the selected cell(s) are slightly darker so you can zoom in on the right location

3) you can always click the FILL button to make the cell(s) yellow (or whatever color you choose) and then click it again and select to remove that fill

Highlighted

@Malcolm McMaster 

 

I believe you could add a macro to your worksheet containing the table to highlight the selected cell.

 

I would also suggest adding a cell to your worksheet, which I named "EnableHighlight," so you could turn it on/off without having  to edit the vba code every time.

 

I attached an example file you can play with. If you right click on the worksheet tab and select "view code," then you will see the macro.

 

If you do add the macro to your own workbook, then be sure to save it as .xlsm (macro enabled) and not a .xlsx or the macro will get stripped out.

Highlighted

@JMB17  Many thanks this is great start.

 

Highlighted

@Malcolm McMaster 

 

If you are solely checking for duplicates, why not use conditional formatting to find and highlight in one easy step?

 

Select data range to be checked then, from the Home tab, select Conditional Formatting, from the drop-down choose Highlight Cells Rules and then Duplicate Values.  Done!