Tom - 12458.html
Posted by Chris D on January 02, 2002 12:23 PM
Hi Tom,
12293.htmlfinding duplicates via conditional formatting
Tom, sorry, this very probably illustrates what you meant by "user error" etc etc !! and how VBA is quicker and easier once written. I'm ploughing through a JWalk VBA book so am about to launch into VBA coding soon as I agree with you about automation and letting excel do the work for you...
With my conditional format option, I took my example (produced at home over Xmas) and pasted it from this bulletin board into a sheet at work, and as you pointed out, it didn't work !
Tonight, I pasted again from this bulletin board, from my own message, and it didn't work !
In the space for the formula that precedes "formula is", when I pasted, it automatically inserted "quotation marks" around the formula
(ie - "this is the formula" rather than just this is the formula)
and the conditional format failed. When I deleted these "quotation marks" the conditional formatting worked fine, all the duplicates adhered to the specified formatting [a nice shade of purple background]
I guess my question is : what is the significance of these "quotation marks" within the formula in the ~formula is~ section of conditional formatting ?
many, MANY thanks if you find the time to address this, I look forward to taking that step into VBA via JWalk, but am resigned to logical functions for the moment !
Chris
:-)
Posted by Tom Urtis on January 02, 2002 1:29 PM
Re: Tom - 12293.html
Chris,
I know this is not what was exactly asked for, but if you use this alternative formula instead, it will conditionally format all cells with the same values.
=IF(COUNTIF($A$1:$A$20,A1)>1,1,0)
The original question was how to conditionally format only those values subsequently that are duplicates to one that was originally entered, but leave the one that was originally entered alone. The 2 ways I know how to do that I suggested in my posts (one using VBA, one manually using Edit GoTo etc without an extra column). Beyond that I'm unable to shed more light on this, aside from the formula suggestion above.
You may hear from someone who knows a conditional format formula to do what you are asking; most things seem possible in Excel. I bet we could conjure up a UDF for instance. When I started learning this stuff 8 years ago, as I encountered a problem that got solved more than 2 ways as this one now has, I'd take a break and then look for other mountains to conquer. If you do find a conditional format for this, please let me know.
Glad you are doing the reading and research; it'll always pay dividends eventually.
Tom Urtis
