Colour formatting without macro

[JRS]

Hi folks,

I'm trying to get my excel spreadsheet to look at column A which has a day count:
Day1
Day2
Day3
...etc...

If the day is an odd number then I would like that row to be coloured with a blue background, if it is an even number then I would like it to be coloured with a yellow background.

Does anyone know how I can do this? Ideally I would like it so the page does this automatically without the need to press a button to run a macro. Again, not sure if I can do this. Please help,

Thanks
JRS

 

[machopicho]

You can use condtional formatting and ISODD formula

 

[Ganjin]

if you know how to use a formula in conditional formatting:

For Column A
1) For Odd Days
=IF(MOD($A1,2)=1,TRUE,FALSE)
2) For Even Days
=IF(AND($A1 <> "",MOD($A1,2)=0),TRUE,FALSE)

if you don't know how to use a formula in conditional formatting:
1) Select Column A
2) Home Tab, Styles, Conditional Formatting, Manage Rules
3) New Rule, Use A Formula To Determine Which Cells To Format
3a) =IF(MOD($A1,2)=1,TRUE,FALSE)
3b) Format, Fill, Background Color (Blue for this one)
3c) Hit Okay
4) New Rule, Use A Formula To Determine Which Cells To Format
4a) =IF(AND($A1 <> "",MOD($A1,2)=0),TRUE,FALSE)
4b) Format, Fill, Background Color (Yellow for this one)
4c) Hit Okay
5) Hit Okay

 

[JRS]

Ganjin, Your code is almost perfect, if I put a number into column A then it works like a dream.

The problem is I need it to work when it literally says "day1" and not just "1"
Is there a way to get it to check just the last digit in cell?

Thanks for your help so far...

 

[JRS]

Also, I need it to colour the whole row, rather than just the first cell.

Anymore advice would be greatly appreciated...

 

[Ganjin]

hmm, assuming that it is always like this:
day1
day2
day101
day202

then, these formulas should work:
=IF(MOD(MID($A1,4,100),2)=1,TRUE,FALSE)
=IF(AND($A1 <> "",MOD(MID($A1,4,100),2)=0),TRUE,FALSE)

 

[Ganjin]

ah, that is a simple fix, instead of selecting Column A in the first step:

either ...
select the whole sheet
(the entire row to the absolute limits of the table will be highlighted)
or ...
select Column A and drag to the last relevant Column
(if your data goes from Column A to Column Z, then dragging Column A to Column Z, will allow for a nicer look)

 

[JRS]

muchos muchos gracias, it works perfectly!!!
:D
Just wish I could figure this stuff out for myself.