Conditional Formatting tweak
Posted by BigOREDNECK on October 19, 2001 11:02 AM
Using excel to track trailers at various customer locations. I'm using conditional formatting to highlight the cells in a column that contain duplicate trailer numbers with the following formula[ =SUMPRODUCT((ISNUMBER(SEARCH(D9,$D$9:$D$142)))+0>1 ] THIS WORKS GREAT, but if there is an empty cell in the range it applies the conditional format to it as well. What do I need to do for condition 2 in order for blank cells not to be highlighted?
Posted by lenze on October 19, 2001 11:14 AM
Put =ISBLANK(Cell) as the first condition and then don't choose any formatting for condition one. This will take precidence over your 2nd condition
Posted by Juan Pablo on October 19, 2001 11:15 AM
Try with (In the same condition)
=(SUMPRODUCT((ISNUMBER(SEARCH(D9,$D$9:$D$142)))+0>1)*(D9<>"")
Or
=(COUNTIF($D$9:$D$142,"="&D9)>1)*(D9<>"")
Juan Pablo
Posted by BigOREDNECK on October 22, 2001 5:24 AM
THAT DID THE TRICK.........THANKS LENZE!!!!!
