Conditional Formatting for Whole Numbers

[Majella]

I have a spreadsheet that contains whole numbers and decimals. How do I use conditional formatting to highlight the cells with whole numbers? Help please!!

[Moo the Dog]

If your range was from a1:z1, select those cells and use this as the CF formula:

=a1=int(a1)

- Moo

[Richard Buttrey]

Hi,

One way would be to test whether a cell's number equals the cell's integer. i.e. =A1=INT(A1)

[Majella]

Thank you but I'm a real beginner here. Can you please break it down even further. How do I input the formula? I have highlighted the group of cells, now what? Your help is much appreciated - thanks!!!

[jeffreybrown]

Based on what you were given from above...

Conditional Formatting

• Highlight applicable range >> $B$2:$I$90
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =B2=INT(B2)
• Format… [Number, Font, Border, Fill]
• OK >> OK

[Majella]

Thanks Jeff, I just followed those steps and for some reason it has highlighted cells with both whole numbers and decimals and left blank cells with both whole numbers and decimals...any advice?

[jeffreybrown]

Check out this sample.

[Majella]

Does the fact that the numbers in the cells were arrived at through a formula have any bearing on this new formula I'm trying to implement?

[jeffreybrown]

Does the fact that the numbers in the cells were arrived at through a formula have any bearing on this new formula

No, the formula should not affect the CF. Do you want to post a sample where maybe we can spot something?

[FDibbins]

all formulas (CF and otherwise) reference the answer in the cell, not the "contents" of the cell, so no, it should not affect it

[Majella]

here's the spreadsheet I'm working on - thanks for your help

I want to highlight the cells between B2 -I90 that contain whole numbers

[Moo the Dog]

Delete the formatting rules you have and create a new one using this formula:

=B2=INT(B2)

- Moo

* Make sure B2:I90 are selected before you create the CF

[jeffreybrown]

First thing, clear all the formats you currently have then follow the update to post #5

[Majella]

THANK YOU!!!! So sorry, it became obvious once I realized A1 was empty!! Thanks for your help everyone!!!

[Moo the Dog]

You're welcome, Majella. Glad we could help.

- Moo, et al.

[jeffreybrown]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.