Conditional formatting

[Howard]

Excel 2000
Last man standing pool where a team can only be picked
one time. For example, if a team is entered in F1 that
was previously entered in A1 thru E1, then I want the
cell color to be blue (anything other than white)so it
will stand out. I've been playing with conditional
formatting, but can't get it to work.

Thanks,
HF

 

[Tom Ogilvy]

in F1

In conditional formatting, change Cell Value is to Formula is and put in the
below formula

=Countif($A$1:E1,F1)>0

Change format to Pattern and Blue.

You can copy this cell and paste, special=>Formats to cells on the left and
right.

 

[David McRitchie]

I expect that what was really wanted was to color cells
in columns B through F, not just column F.

Select columns B:F as the columns that could be colored,
you could select A:F but A would never be colored with the
following conditional formatting formula.

A1 should be the active cell because the formula will be based
on that cell.
Condition 1 Formula is: =COUNTIF($A1:A1,A1)>1
use the format button to format Font color and or
interior color (pattern color).

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[Howard]

Thanks very much to both of you. I wasn't even close!
Howard

-----Original Message-----
I expect that what was really wanted was to color cells
in columns B through F, not just column F.

Select columns B:F as the columns that could be colored,
you could select A:F but A would never be colored with the
following conditional formatting formula.

A1 should be the active cell because the formula will be based
on that cell.
Condition 1 Formula is: =COUNTIF($A1:A1,A1)>1
use the format button to format Font color and or
interior color (pattern color).

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

in F1

In conditional formatting, change Cell Value is to Formula is and put in the
below formula

=Countif($A$1:E1,F1)>0

Change format to Pattern and Blue.

You can copy this cell and paste, special=>Formats to cells on the left and
right.

.

 

[David McRitchie]

Hi Howard,
You were probably closer than you thought you were, you have
Conditional Formatting in the subject and you can define the problem
fairly reasonably, which makes things easier for others to answer.
You would be able to answer your question as well with a little practice
looking in HELP and in the Google Archives and hopefully my page
on Conditional Formatting or other web pages.

 

[BlackNLovesBLondes]

I expect that what was really wanted was to color cells
in columns B through F, not just column F.

Select columns B:F as the columns that could be colored,
you could select A:F but A would never be colored with the
following conditional formatting formula.

A1 should be the active cell because the formula will be based
on that cell.
Condition 1 Formula is: =COUNTIF($A1:A1,A1)>1
use the format button to format Font color and or
interior color (pattern color).

More information on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

in F1

In conditional formatting, change Cell Value is to Formula is and put in the
below formula

=Countif($A$1:E1,F1)>0

Change format to Pattern and Blue.

You can copy this cell and paste, special=>Formats to cells on the left and
right.

Visit HTTP://HOT.FORBIDDEN.DE ! For FORBIDDEN action!

Visit HTTP://HOT.FORBIDDEN.DE ! For FORBIDDEN action!

Visit HTTP://HOT.FORBIDDEN.DE ! For FORBIDDEN action!