Condtional Formatting Problem

Posted by sean on September 10, 2001 4:22 AM

I have a spreadsheet in which bookings are made for booking taxis, sevral times a taxis has been booked twice for the same journey. I have been trying to set conditional formatting so when a date/time is added which already exists the cell changes colour but todate have not had much luck. any help would be appreciated

Sean

Posted by Tom Urtis on September 10, 2001 4:49 AM

The conditional format formula is:
=IF(COUNTIF($B$1:$C$20,B1)>1,TRUE,FALSE)
assuming range B1:C20.

You may want to consider data validation to stop the duplication error at the point of data entry. From the above example, click in cell B1, then click Data > Validation > Allow Custom, and enter the formula:
=COUNTIF($B$1:$B$20,B1)=1
then Copy > Paste special, Validation down to B20.

Tom Urtis

Posted by Aladin Akyurek on September 10, 2001 5:42 AM

Tom --

=COUNTIF($B$1:$C$20,B1)>1

would suffice.

A similar method is described at:

http://www.mrexcel.com/tip008.shtml

Aladin

Posted by Tom U on September 10, 2001 5:56 AM

Thanks Aladin