conditionalk format to highlight duplicate numbers only

[still learning]

Hi
Row B has both text and numbers.
When I highlight B row, and use conditional format to change the font color of any duplicate values, it highlights text and numbers.
How do I only highlight numbers.

I don't care if the text is a duplicate. I'm only concerned with the numbers.


mike

 

[philwojo]

Are you asking that if a cell contains say "AA123", and it is a duplicate in another cell that you only want to highlight the 123 portion of that cell?

If that is the case you are unable to do that with Conditional Formatting (CF).

If you are asking something else please restate your question.

 

[still learning]

hi philwojo

Some cells in row B have only a text
Some cells in row B have only a number
I don't want to duplicate any number in the row
I don't care if any of the text is duplicated
Example

B1..abc
B2..def
B3..abc
B4..abc
B5..123
B6..def
B7..def
B8..123

since B8 has the same number as a cell above I want to highlight it. to show me its a duplicate number. i will then know i made an error that the number is wrong.

I'm only concerned with numbers.

mike

 

[philwojo]

Try this added as a new CF formula, first highlight the range of values you want to check, then add the new rule as a formula.

=IF(ISNUMBER($B1),IF(COUNTIF($B$1:$B$8,$B1)>1,TRUE,FALSE))

You will need to update the $b$1:$b$8 portion to reflect your actual full range.

 

[pjmorris]

try this as a conditional formatting formula in cell B1 and copy the formatting down. It will highlight the second or subsequent occurrences of a number: =AND(ISNUMBER(B1),COUNTIF($B$1:$B1,B1)>1)

Hope that helps.

Regards

 

[still learning]

Thank you philwojo:

Your formula works great. It highlights both duplicate numbers

Thank you pjmorris:

Your formula also works great

again thanks


mike

 

[pjmorris]

delighted to have helped and thanks for the feedback.