How to search for a number

[Bryan McNally]

I want to enter a number into a cell and have excel search a grid of
numbers and highlight any instances of the number it finds. What formula
will do this?

 

[Peo Sjoblom]

No formula but conditional formatting will do that
assume you have a table from A1 to E10
you put the number in cell F2

select the table with A1 as the active cell,
do format>conditional formatting, cell value is equal to $F$2
click the format button, select patterns and a highlight colour
click OK twice

 

[Bryan McNally]

I guess I need to provide more information. Your reply does work, but
only for one cell. I want to enter numbers into 6 different cells and
have them search a grid of numbers and hightlight each result.

 

[Max]

Assume your master set of 6 numbers is in A1:F1
and your grid of numbers to be checked is in say A2:F100

Select A2

Click Format > Conditional Format

Under Condition1, make the settings:
---------------------------------------------
Formula Is: | =COUNTIF($A$1:$F$1,A2)>0
Click Format button > Patterns tab > Green? > OK
Click OK at the main dialog

Now to copy the conditional formatting .. :

With the formatted cell A2 selected,

Double-click on the Format Painter icon ("brush")
[cursor will turn into "brush"]

Click to select [i.e. "paint" over] the grid A2:F100

Press Esc to revert the cursor to normal