Conditional formatting problem

Firecop1348

Board Regular
Joined
Oct 24, 2009
Messages
101
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"
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top