Conditional Format- Highlight Numbers with 00's on the end.

[ricey97]

I have a long list of numbers that are to 7 significant figures.

I want to highlight the numbers that end in 00 and 000. I don't want to change their number format or significant figures. For example, values such as 1.2345000 and 1.2345600.

Is there a formula for conditional formatting that could highlight that column of values? A macro? Anything?

Anything I have searched has not worked.

Many thanks in advance.

 

[Joe4]

Welcome to the Board!

For example, values such as 1.2345000 and 1.2345600.

Are these values formatted as numbers or as text?
If numbers, those trailing zeroes aren't really there. Note that changing the formatting of a cell does not change the actual value in the cells, just how it is displayed. If you look in the formula bar, it will show you the number exactly as Excel sees it.

How we approach this depends on whether the entry is actually numeric or text. So we really need to know that.

 

[ricey97]

Welcome to the Board!


Are these values formatted as numbers or as text?
If numbers, those trailing zeroes aren't really there. Note that changing the formatting of a cell does not change the actual value in the cells, just how it is displayed. If you look in the formula bar, it will show you the number exactly as Excel sees it.

How we approach this depends on whether the entry is actually numeric or text. So we really need to know that.

They are formatted as numbers, but I guess I could format it as text.

 

[Joe4]

If you format the cells as text, and make sure that those trailing zeroes are really there, then it is pretty easy with Conditional Formatting.
You would just use the RIGHT function, .e.

=RIGHT(A1,2)="00"

 

[Fluff]

If you want to keep them as numbers, maybe

+Fluff 1.xlsm
	D
1
2	1.2345
3	1.23456
4	1.234567
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D4	Expression	=RIGHT(TEXT(D2,"0.0000000"),2)="00"	text	NO

 

[Joe4]

Clever solution, Fluff!

 

[ricey97]

If you format the cells as text, and make sure that those trailing zeroes are really there, then it is pretty easy with Conditional Formatting.
You would just use the RIGHT function, .e.

=RIGHT(A1,2)="00"

+Fluff 1.xlsm
	D
1
2	1.2345
3	1.23456
4	1.234567
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D4	Expression	=RIGHT(TEXT(D2,"0.0000000"),2)="00"	text	NO

Thank you both so much! I did go for Fluff's suggestion in the end. Have a good day!

 

[Fluff]

Glad we could help & thanks for the feedback.