Conditional formatting row when cell value is an even number
 

I apologize if this is a duplicate post, not sure what happened to the post I
was working on.

I have a spreadsheet with a column of numbers... the rest of the data is
text or dates. What I am trying to do is shade the row that contains an even
number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing
only the column with the numbers, and using the following in the conditional
format:
=MOD($A1,2)=0
However, when I try to select all and apply the exact same formula (thinking
it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?

Thanks!

Try

=AND(MOD($A1,2)=0,ISNUMBER($A1))

note that dates are numbers


Regards,

Peo Sjoblom

"hhunt" wrote in message
...
I apologize if this is a duplicate post, not sure what happened to the
post I
was working on.

I have a spreadsheet with a column of numbers... the rest of the data is
text or dates. What I am trying to do is shade the row that contains an
even
number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing
only the column with the numbers, and using the following in the
conditional
format:
=MOD($A1,2)=0
However, when I try to select all and apply the exact same formula
(thinking
it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?

Thanks!

I'm not sure where you're going wrong with this. I just tried it on a new
sheet and seems to work.

These are the steps I took. Maybe you did something different which changes
the way it works.

In column starting at row 1 I entered numbers 1 to 10
I selected A1 and entered your conditional formatting Condition 1 as Formula
is then pasted your formula into it and selected the required format.
I copied the cell, selected the entire sheet and did a Paste Special,
pasting format only.
Rows 2,4,6,8 & 10 were all shaded as were rows 11 and below as a blank cell
is seen as 0.

"hhunt" wrote in message
...
I apologize if this is a duplicate post, not sure what happened to the post
I
was working on.

I have a spreadsheet with a column of numbers... the rest of the data is
text or dates. What I am trying to do is shade the row that contains an
even
number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing
only the column with the numbers, and using the following in the
conditional
format:
=MOD($A1,2)=0
However, when I try to select all and apply the exact same formula
(thinking
it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?

Thanks!

Works fine for me.

Select a bunch of rows starting row 1 and then do Format / Conditional
Formatting, change 'cell value is' to 'formula is' and either use your
formula or perhaps =AND($A1<"",MOD($A1,2)=0) if you don't want the blank
rows to appear as flagged.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"hhunt" wrote in message
...
I apologize if this is a duplicate post, not sure what happened to the
post I
was working on.

I have a spreadsheet with a column of numbers... the rest of the data is
text or dates. What I am trying to do is shade the row that contains an
even
number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing
only the column with the numbers, and using the following in the
conditional
format:
=MOD($A1,2)=0
However, when I try to select all and apply the exact same formula
(thinking
it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?

Thanks!

Thanks to all of you for your answers - don't know what I was doing wrong,
but the formulas you suggested worked and got rid of the shading on the blank
rows!

Thanks.

"Ken Wright" wrote:

Works fine for me.

Select a bunch of rows starting row 1 and then do Format / Conditional
Formatting, change 'cell value is' to 'formula is' and either use your
formula or perhaps =AND($A1<"",MOD($A1,2)=0) if you don't want the blank
rows to appear as flagged.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"hhunt" wrote in message
...
I apologize if this is a duplicate post, not sure what happened to the
post I
was working on.

I have a spreadsheet with a column of numbers... the rest of the data is
text or dates. What I am trying to do is shade the row that contains an
even
number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing
only the column with the numbers, and using the following in the
conditional
format:
=MOD($A1,2)=0
However, when I try to select all and apply the exact same formula
(thinking
it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?

Thanks!

You're welcome, and thanks for the feedback. The blank cells got shaded
because a blank divided by 2 gives 0, hence your formula thought it was
valid :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip