Hi Harshad,
I'm not sure how Duplicate and how lastcell comes into the mix,
but creating something dependent on color is a bad idea.
1) Very inefficient
2) Some people are color blind
3) Even 8 of 16 basic colors may not be distinguishable on a laptop from most
viewing angles (parts of a screen) by a person..
4) There is no Event to recognize a change of color (or any formatting), so
everything has to be recalculated everytime a recalculation takes place
with use of volatile if you have to try to keep up with changes.

Chip Pearson has a page with routines on determining and counting colors
please read the bold type on the page.
Functions For Cell Colors
http://www.cpearson.com/excel/colors.htm

Some of my examples using Chip's routines:
"Interior Color, using Count, SUM, etc. (#count)"
http://www.mvps.org/dmcritchie/excel/colors.htm#count
--
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

"Harshad" wrote in message ...
i have an excel sheet in which there are several rows out of which some are
red. those which are red, in the last cell i need to have a "yes" or
"Duplicate" next to the cell.................