Cell searching and retrieving

[fluci]

Ok I have Cell A1 as Test
Cells A2:A6 are random words
Cells A7:A10 are Test
And cells A11:A13 are random words again

I want to search it so that When I type in a word in Cell A1 it will search through all of Column 1 and change the background colour of any other cells that have the same word
is that possible?

[Rowan]

The easiest way would be to use conditional formatting. Select range A2:A13.
Format > Conditional Formatting. Formula Is =$A$1. Choose your formatting and
click OK.

Or you can use a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim endRow As Long
Dim chkVal As String
Dim counter As Long
If Target.Address = "$A$1" Then
chkVal = LCase(Target.Value)
endRow = Cells(Rows.Count, 1).End(xlUp).Row
Columns(1).Interior.ColorIndex = False
For counter = 2 To endRow
If LCase(Cells(counter, 1).Value) = chkVal Then
Cells(counter, 1).Interior.ColorIndex = 35
End If
Next counter
End If
End Sub

This is worksheet event code. Right click the sheet tab, select view code
and paste the macro.

Hope this helps
Rowan

"fluci" wrote:

>
> Ok I have Cell A1 as Test
> Cells A2:A6 are random words
> Cells A7:A10 are Test
> And cells A11:A13 are random words again
>
> I want to search it so that When I type in a word in Cell A1 it will
> search through all of Column 1 and change the background colour of any
> other cells that have the same word
> is that possible?
>
>
> --
> fluci
> ------------------------------------------------------------------------
> fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
> View this thread: http://www.excelforum.com/showthread...hreadid=392780
>
>

[Jim May]

Select or highlight the cells you wish to check.
then at the menu, Format, conditional format,
select Cell Value Is, in blank box type test, choose format, pattern,,OK,
out
should work,,

"fluci" <[email protected]> wrote in
message news:[email protected]...
>
> Ok I have Cell A1 as Test
> Cells A2:A6 are random words
> Cells A7:A10 are Test
> And cells A11:A13 are random words again
>
> I want to search it so that When I type in a word in Cell A1 it will
> search through all of Column 1 and change the background colour of any
> other cells that have the same word
> is that possible?
>
>
> --
> fluci
> ------------------------------------------------------------------------
> fluci's Profile:
> http://www.excelforum.com/member.php...o&userid=25896
> View this thread: http://www.excelforum.com/showthread...hreadid=392780
>

[Jim May]

Sorry, i think i missed your q,
You need to use the Formula Is Option and
in the box (with A2 the active cell) enter:
=A2=$A$1 ,, then format pattern..


"Jim May" <[email protected]> wrote in message
news:VRnIe.6419$MZ6.2971@lakeread01...
> Select or highlight the cells you wish to check.
> then at the menu, Format, conditional format,
> select Cell Value Is, in blank box type test, choose format, pattern,,OK,
> out
> should work,,
>
> "fluci" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Ok I have Cell A1 as Test
>> Cells A2:A6 are random words
>> Cells A7:A10 are Test
>> And cells A11:A13 are random words again
>>
>> I want to search it so that When I type in a word in Cell A1 it will
>> search through all of Column 1 and change the background colour of any
>> other cells that have the same word
>> is that possible?
>>
>>
>> --
>> fluci
>> ------------------------------------------------------------------------
>> fluci's Profile:
>> http://www.excelforum.com/member.php...o&userid=25896
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=392780
>>
>
>

[fluci]

will this automatically update whenever i change a cell say... A8?

[fluci]

ok sweet it does work!
thanks