Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching and Retrieving data from a Worksheet | Excel Worksheet Functions |