Conditional Formating based on many cells

[basic]

I am trying to create a conditional format that will look at 30 cells and if
any one of the cells matches the current cell it should change the color of
the number.

Example: Each cell contains the following number
A B C D E
1 1 2 5 1 2
2 3 4 2 3 4
3 5 6 1 5 6
4 1 2 5 2 5
5 3 4 2 4 2
6 5 6 1 6 1


15 3

In cell A15 I would like a conditional format that would take the number in
cell A15 and check to see if it is in the area of A1..E6. If there is one
number that matches the number in A15 then I would like the number in A15 to
change to a color, if not it remains unchanged. The numbers could be spread
out throughout the spreadsheet so I would need to reference 30 specific cells
not just the range.

 

[Sheeloo]

Try
=(COUNTIF($A$1:$E$6,A15))=1
as the formula in the FORMULA IS option of Conditional Formatting and choose
the formatting you want

You can enter it for any cell and then paint on other cells you want to
highlight.

 

[vezerid]

Select A15, Conditional Formatting and:

Formula Is:
=COUNTIF($A$1:$E$6,A15)

HTH
Kostis Vezerides

 

[Shane Devenshire]

Hi,

To conditionally format your cell(s):

1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:

=OR(A15=$A$1:$E$6)

5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[basic]

Thanks for your help. I have tried this and it works fine if your range is
all together, but my 30 numbers are all over the spreadsheet and can't be
grouped together. I tried to adjusted the formula to address each individual
cell and it worked fine until I hit around the 17th cell. It must have a max.

 

[Sheeloo]

One way out is have a contiguous group of cells somewhere on your sheet and
enter the address of one of your separate cells in one of them, then refer
that range in your conditional formula

if you have cells A1, B5, D9,...

then enter this
=A1
=B5
=D9... in Z1, Z2, Z3... for example