Conditional format based on Even/Odd????

I want to be able to change the font color of the numbers in a column based on even or odd. I selected the columns and formated them to all be blue... thats the color I want for even, but now I can't figure out how to set a condition that checks for odd numbers and changes them to red.
With previous help from forum members I now have a count of even/odd for all records, and even/odd for the last 30, this provided some very interesting results.

If you have numbers in A1:A100, select that range and use this formula in Conditional Formatting

=MOD(A1,2)

format as red

Originally Posted by farmerTom

I'm glad I asked this question... that formula doesn't work.

Really?

What sort of "numbers" do you have in your range? Assuming you have only integers in B1:B163 then if you select that range, with B1 active cell, and use the formula

=MOD(B1,2)

and apply red formatting then all your odd numbers will be highlighted in red. Do you want me to post an example spreadsheet?

Originally Posted by daddylonglegs

Really?

What sort of "numbers" do you have in your range? Assuming you have only integers in B1:B163 then if you select that range, with B1 active cell, and use the formula

=MOD(B1,2)

and apply red formatting then all your odd numbers will be highlighted in red. Do you want me to post an example spreadsheet?

Excel Help Quote:
MOD
See Also

Returns the remainder after number is divided by divisor. The result has the same sign as divisor.

Syntax

MOD(number,divisor)

Number is the number for which you want to find the remainder.

Divisor is the number by which you want to divide number.

Any positive int. /2 will have a remainder of 1 if its odd, and 0 if its even.


The formula

Please Login or Register  to view this content.

should have returned 0 or 1 in each cell but didn't. ?????????
A couple of weeks ago I didn't know anything about Excel, now I know very little. The code with the the condition(<>) of doesn't equal zero worked right away. ?????

Please Login or Register  to view this content.

I didn't format the cells as numbers... they are default / general. I may have done something else wrong too, I don't know enough and didn't check. When I re-make the spread sheet I'll import the data as numbers.

0=False, and 1=True is boolean and I didn't think that Excel was treating this as a conditional answer, thats why I thought the <> were needed. And I can see that the formula

Please Login or Register  to view this content.

will test as True too. I'm starting to understand this a little better now.

Thanks to all of you....

Tom

Hello farmerTom, I've suggested a simple working method twice now but I'm not sure you've implemented it as suggested.

Let's be clear, you're using the formula within conditional formatting, right? Not on the worksheet?

I never suggested using the formula

=MOD(B1:B163,2)

I suggested selecting the range B1:B163 and applying the formula

=MOD(B1,2)

If you do that the formula will automatically adjust for each cell in the range, so the condition will automatically adjust to =MOD(B10,2) in cell B10 or =MOD(B163,2) in B163.

As you know, assuming the cell contains a positive integer, this formula will return a 1 if integer is odd or a zero if integer is even. 1 is effectively TRUE within CF and 0 is FALSE so this has the effect of formatting odd numbers, which is what you said you wanted to do, no?

Using a formula like

=MOD(B1:B163,2)

in isolation is pointless, it may work in some circumstances but it's use betrays a lack of understanding of the results it gives. This formula returns an array but unless used within another function, e.g. SUM, you won't be assessing any value within it except the first.

Note: it won't make any difference if the cells are formatted as number or text

I've attached an example

daddylonglegs can I select the column because I add a row everyday, 6 days a week? I have to go through and change some of the funtions as is so I don't have to fill up all the formula everyday.

Never mind tried it and it works ... thanks.

Select column
Open the Conditional format wizard
Do formula is
=MOD(B1,2)
set format to red text
works.

Worked a Treat on my problem.

=MOD($S10,2)

With a $mark to cover an area rather than a single column