Highlighting cells when using Find

[RalphA]

When in Word, if I press Ctrl+f, a "Find" window pops up, and allows me to enter something I wish to find, say, the contiguous characters cel.com In this case, if it exists, the characters found, cel.com are highlighted, using Word's method of swapping the backround color with the foreground color, which stands out very clearly. By the way, this text editor works the same way as Word. Try it!

When in Excel, Ctrl+f will find, say, the contiguous characters 234, if they exist, and they are highlighted, using Excel's method of surrounding the cell with the characters with a dark border.

I have, in Excel, a fairly busy sheet, in which many of the cells have a dark, black border surrounding them, so, when I use the Ctrl+f function, I have one tough time deciding which is the higlighted cell!

Without changing anything on my spreadsheet, is there a way to change how Excel's highlighting appears, say by doing it the Word way, swapping foreground and background colors, or some other, easily visible way?

 

[IML]

band-aid:
the current cell is shown in the name box in the top left corner just above the uppercell on the left.

 

[RalphA]

Thank you, IML, for your observation. It will help! But, I would still like to have something more like the inverted colors. Maybe you have some ideas on this?

 

[NateO]

Howdy Ralph, you might want a vba proc. for something like this. Something like:

<font face=Courier New><SPAN style="color:darkblue">Option</SPAN> <SPAN style="color:darkblue">Explicit</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> Fin_For()
<SPAN style="color:darkblue">Dim</SPAN> OrigCl <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, Cl <SPAN style="color:darkblue">As</SPAN> Range, n <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, OrColr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
n = InputBox("Please enter a search string", "Input Request")
<SPAN style="color:darkblue">If</SPAN> n = "" <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
<SPAN style="color:darkblue">Set</SPAN> Cl = Cells.Find
<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> Cl <SPAN style="color:darkblue">Is</SPAN> <SPAN style="color:darkblue">Nothing</SPAN> <SPAN style="color:darkblue">Then</SPAN>
    OrigCl = Cl.Address
    <SPAN style="color:darkblue">Do</SPAN>
        OrColr = Cl.Interior.ColorIndex
        <SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> OrColr = 6 <SPAN style="color:darkblue">Then</SPAN> Cl.Interior.Color = vbYellow Else _
            Cl.Interior.Color = vbGreen
        Cl.<SPAN style="color:darkblue">Select</SPAN>
        <SPAN style="color:darkblue">If</SPAN> MsgBox("String found in Range " & Cl.Address(False, <SPAN style="color:darkblue">False</SPAN>) & _
            Application.Rept(Chr(10), 2) & "Shall we find the next match?", _
                vbYesNo) = vbYes <SPAN style="color:darkblue">Then</SPAN>
            Cl.Interior.ColorIndex = OrColr
            <SPAN style="color:darkblue">Set</SPAN> Cl = Cells.FindNext(Cl)
        Else: Cl.Interior.ColorIndex = OrColr
            <SPAN style="color:darkblue">Set</SPAN> Cl = <SPAN style="color:darkblue">Nothing</SPAN>
            <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">Loop</SPAN> <SPAN style="color:darkblue">Until</SPAN> Cl.Address = OrigCl
    <SPAN style="color:darkblue">Set</SPAN> Cl = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>


Shouldn't permanently alter your cell contents. Also note, the column and row headers are bolded when using a conventional Find method.

Hope this helps. Have a nice weekend.

 

[RalphA]

Nate: Thank you for all the trouble you went to in order to present a VB coding solution. I have been studying VB for a few months now, and am having fits with so many words, commands, methods, procdures, properties, and so on. I am sort of turned off of VB, at least for the forseeable future.

HOWVER: Your pointing out that the row "header" numbers and the column header letters are bolded when Find locates the string entered, seems like a real winner! It's not that bad to look down the left side, where the row number-identifiers are, and find the bolded one, then, go to the cell with the column header bolded! This will alleviate this task for me, thanks to you. :D Enjoy your weekend, too!