selecting cells

[whitey987]

simple question, how do you select all the cells that contain a value, e.g. London??

 

[NBVC]

What do you mean by select? Highlight them?

 

[whitey987]

yeah, what im trying to do i create a search button on a user form.

 

[Banker1]

You could do something like this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Highlight()
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Set</SPAN> WorkRange = Range("A1", ActiveCell.SpecialCells(xlCellTypeLastCell))

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> UCase(Cell.Value) = "LONDON" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Cell.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> Cell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I set the Cell value to uppercase in the code and match it to an uppercase string so that you capture all types of "London" (i.e. London, london, LONDON)

I would set up a list elsewhere (another sheet in the workbook or in your personal workbook) containing all the values you'd like to highlight.

You could also consider setting up an input box which will allow a user to select the values he/ she would like to highlight.

 

[just_jon]

Looping is one way, using Do/Find/While another.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, c <SPAN style="color:#00007F">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> c = .Find("YOUR TARGET VALUE HERE", LookIn:=xlValues, LookAt:=xlWhole)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        FirstAddress = c.Address
        <SPAN style="color:#00007F">Do</SPAN>
            <SPAN style="color:#007F00">' Do whatever it is you want when found here.</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> FirstAddress
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[whitey987]

thanks alot, that really helped. Cheers