Conditional formating - highlight every integer

**pluszak** · 04-15-2009, 04:37 PM

Hi guys,
I'm using MS Excel 2003. I've got a task to do. It is to highlight every integer in a table which contains over 500 entries. I'd be grateful if anybody could help.
I guess I ought to use conditional formating. However I don't know quite how :p

Greetings,
Pluszak

**teylyn** · 04-15-2009, 05:07 PM

Hi,

select the entire range with numbers, then select conditional formatting. In the dialog box choose "Formula Is" from the left dropdown and enter this formula in the formula field

=MOD(A1,1)=0

replace the A1 in the above with the first cell in your range, but make sure there are no $ signs in the cell reference. Then select how you would like the integers formatted and click OK

cheers

**pluszak** · 04-17-2009, 04:16 PM

Thx a lot! It works. However I needed to change the formula a little bit. It is =MOD(A1;1)=0 with a ; instead of :

And btw I've got one more question. Is there any kind of restriction in MS Excel 2003 on the magnitude of a number? 'Cause if I enter a number, for instance, 123456789123456789 and click enter than Excel rounds that number down to 123456789123456000. What's the matter? Is there any way to solve this problem?

**teylyn** · 04-19-2009, 06:08 PM

Hi again,

first: whether or not Excel uses the comma or the semicolon in formulas is dependent on the computer's language and regional settings, especially the list indicator. Most European regional settings specify the semicolon as the list indicator, and this is what Excel then expects in formulas. In many other countries, the comma is used instead. My setup uses commas, yours uses semicolons. So, whatever formula I copy and paste from Excel, you would have to replace all commas with semicolons for the formula to work on your computer.

Second: Excel does have limits as to how big a number can be. A number with more than 15 digits will result in the remaining digits being rounded to zero. It's just the way Excel works. It cannot store any bigger number with better precision.

hope that helps

**daddylonglegs** · 04-19-2009, 07:00 PM

If you want to enter numbers greater than 15 digits then you need to enter them as text so that they don't change.

Either format the cells as text before you input, or precede the entry with an apostrophe (which tells excel that what follows is text), e.g. enter

'123456789123456789

Excel won't display the apostrophe.

Note: you can't use the numbers in calculations otherwise they'll revert to 15 significant digits......but it's a solution if you just want to display them.....