Cell searching and retrieving
 

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

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

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" wrote in
message ...

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

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" 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" wrote in
message ...

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

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


--
fluci
------------------------------------------------------------------------
fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=392780

ok sweet it does work!
thanks


--
fluci
------------------------------------------------------------------------
fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896
View this thread: http://www.excelforum.com/showthread...hreadid=392780