Conditional formatting problem

[Firecop1348]

I am trying to make it to were if i enter the same value into a column that it will turn the cell RED.
Example
A1=1
A2=2

A3=1 Cell would turn RED along with A1
<CELL turn would a1 and a3 p red.<>ECT. ECT..
Using conditional I can do this BUT it wont let me select a range of cells. I get the ERROR "You can not use a direct reference to a worksheet range in a conditional formatting formula." I need to have this work for 6 cells. I got it to work on my home computer with EXCEL 07 but at work we only have excel 03. What if anything can I do to make this work on 03. If I make the sheet using 07 and save it in 03 format would it still work?
Thank you for all your help.
"Conditional formatting: Condition 1. Cell value is >equal to>=$i$2:$I$11> format Cell turns red"

 

[T. Valko]

I am trying to make it to were if i enter the same value into a column that it will turn the cell RED.
Example
A1=1
A2=2

A3=1 Cell would turn RED along with A1
<CELL p turn would a1 and a3 red.<>ECT. ECT..
Using conditional I can do this BUT it wont let me select a range of cells. I get the ERROR "You can not use a direct reference to a worksheet range in a conditional formatting formula." I need to have this work for 6 cells. I got it to work on my home computer with EXCEL 07 but at work we only have excel 03. What if anything can I do to make this work on 03. If I make the sheet using 07 and save it in 03 format would it still work?
Thank you for all your help.
"Conditional formatting: Condition 1. Cell value is >equal to>=$i$2:$I$11> format Cell turns red"

I'm not sure what your condition is but you should be using the "Formula Is" option.

Tell us the EXACT range of cells you want to format and tell us very specifically what the condition is.

 

[Firecop1348]

Range A3-A8, If I enter the same number or letter in any cell A3-A8 the cell would turn red showing me that I made a mistake and have to change one of the values in the red cells. I need this to work in 6 columns but if I get the first one down I can change the formula to work with the others.

 

[Firecop1348]

The condition would be that if any cell in the range of A3 - A8 has the same value the two cells with the same value would turn red.

 

[T. Valko]

The condition would be that if any cell in the range of A3 - A8 has the same value the two cells with the same value would turn red.

Try this...

Select the *entire* range A3:A8 starting from cell A3. Cell A3 will be the
active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the menu Format>Conditional Formatting
• Select the Formula Is option
• Enter this formula in the box on the right:
• =COUNTIF(A$3:A$8,A3)>1
• Click the Format button
• Select the desired style(s)
• OK out

 

[Firecop1348]

That works GREAT! Thank you. If I wanted to skip a cell like go from A3 to A5 to A6 ect. so I can have a little space between the cells how would that work.
Thanks again.

 

[T. Valko]

That works GREAT! Thank you. If I wanted to skip a cell like go from A3 to A5 to A6 ect. so I can have a little space between the cells how would that work.
Thanks again.

Well, that complicates things!

You'd have to apply conditional formatting to each separate range.

Let's assume you want to format A3 and A5:A6.

The formula for A3 would be:

=COUNTIF(A$5:A$6,A3)+(A3=A3)>1

The formula for A5:A6 would be:

=COUNTIF(A$5:A$6,A5)+(A$3=A5)>1

 

[Firecop1348]

Thanks, I can handle it from here. Leave it to me to get things complicated. LOL ..... I have learned a lot from this site and books but somethings ya just need the pro's.

 

[T. Valko]

Thanks, I can handle it from here. Leave it to me to get things complicated. LOL ..... I have learned a lot from this site and books but somethings ya just need the pro's.

Good deal. Thanks for the feedback!