Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting row if cell value in certain column is even | Excel Worksheet Functions | |||
Conditional Formatting (Date vs Number) | Excel Discussion (Misc queries) | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |