How to search for a number

  • Thread starter Thread starter Bryan McNally
  • Start date Start date
B

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?
 
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
 
Assume that it is cell A! that you will put in the number you wish to search
for, and that the grid is A5:H50. Select A5:H50, do Format / Conditional
Formatting, then make the condition 'Cell Value' 'is equal to' and select cell
A1 with the range selector (Should wrap it with $ signs, eg $A$1) - Hit the
format button, choose a nice colour from the patterns tab and hit OK. Now
whatever number you put in cell A1, every cell in the range A5:H50 that matches
it will flag up automatically.
 
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.
 
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
 
Back
Top