I'm trying to highlight dupe rows in Excel '03. In column Q I have concatenated all of the cells I want to check. For example, an entry in cell Q1 might look like:

John Doe 300 Whit Drive Pittsburgh PA 00001-5235

To highlight dupe rows of data, I am using condition formatting with the the following formula/conditions to highlight the row yellow, but it's not working:

=(IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1)))>1

However, when I place a similar formula into cells... =IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1)) ..., the values return properly as either 0 or X (number).

Can anyone see where my problem might be and why my rows will not highlight when I place the same formula in conditional formatting? I have a feeling it has to do with choosing absolute values???

Any help would be appreciated!

In the expression get rid of the = sign in the conditional formatting.

Its not the = but the >1 is outside the brackets
=(IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1)))>1
shouldb
=(IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1))>1)

Brian

That may be true Brian, but in the Conditional formatting it is ASSUMED by it that you are using a condition. You do not include the = sign for it.

If you don't put it in the system does, that is where the assumption comes in, but not when using formula is you need it or it converts your formula to ="yourformula"

Brian

If you don't put it in the system does, that is where the assumption comes in, but not when using formula is you need it or it converts your formula to ="yourformula"

Brian
I'm talking about the Conditional Formatting - you know that part? You choose Expression IS and then you put the expression. You don't put the = sign in front of it and that is what the OP was using - Conditional Formatting, not a formula in a cell (when then you do need the = sign of course). :)

And of course you can use the = IN the expression, it just should not be the beginning symbol when using the feature - Conditional Formatting.

Please don't insult me I can read, I can also test and have just done so it maybe different in later releases but in 2002 you need the =.

Brian

Please don't insult me I can read, I can also test and have just done so it maybe different in later releases but in 2002 you need the =.

Brian

Are you including that when using EXPRESSION IS as well as VALUE IS?

I've always seen errors when using the = at the beginning with Expression Is.

Oh well, count me as a pickle with no brain. I'm sitting here thinking of Access Conditional Formatting and this was Excel. :(

I shall now go dunk my head in a hot vat of oil and paint myself pink.

Oh well, count me as a pickle with no brain. I'm sitting here thinking of Access Conditional Formatting and this was Excel. :(

I shall now go dunk my head in a hot vat of oil and paint myself pink.

LOL, we all make mistakes, the good guys admit it.

Brian