Find and replace with formatting

D

Del

I am using Excel 2000.
I have a large range of numbers and need to identify if
numbers entered in a separate, smaller range are included
in the large range and then change the format of only the
included numbers so that they are easily identifiable to
users looking at the large range.
There are more and more numbers being manually added
weekly to the smaller range.

I hope this makes sense, I have tried using macros but
can't seem to get anywhere near.

Thanks for reading this.
 
P

Paul

Del said:
I am using Excel 2000.
I have a large range of numbers and need to identify if
numbers entered in a separate, smaller range are included
in the large range and then change the format of only the
included numbers so that they are easily identifiable to
users looking at the large range.
There are more and more numbers being manually added
weekly to the smaller range.

I hope this makes sense, I have tried using macros but
can't seem to get anywhere near.

Thanks for reading this.
Click to expand...

You can do this with Conditional Formatting (on the Format menu).
For the sake of example, suppose your large range is A1:A100 and your small
range is B1:B5.
Select A1:A100.
Format > Conditional Formatting.
Change the first box to "Formula Is:"
In the second box, put the formula
=(OR(A1=$B$1:$B$5))
Note that you must use a relative reference for A1 and absolute for the
small range.
Press Format, choose the required format and press OK twice.

Alternatively, as you say you will be adding numbers to the small range, you
could name this with a dynamic range reference and use that name in the
formula. For example, define the name SmallRange as
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
and then the conditional format formula will be
=(OR(A1=SmallRange))
 
Top