Excel 2007 : Conditional formatting

**Skyshield2012** · 01-04-2012, 08:15 AM

Hi,
I tried to search but the results did not show any information.
Not sure if i did search correctly though.

I searched for conditional format and nothing came up in the search.

I am working on a sheet for a colleague.
Basically it is that i want the cell to have a color and output a text when the conditions are met.

the formula i use is this one

=IF(AND(G2="AIR"; I2<=TODAY());"Missing";"")

If G2 is AIR and I2 same or less than todays date then condition is True (missing)

I have put that formula in a cell and it works but when i copy it to the conditional format tool where you can add the formula and tell it the color to have it does not work.

Why is that?

i am rather new and i am trying to learn on my self here with excel and tiping changing the formula until it works. :-)

Apreciate your help in this matter.

Thanks in advance.

Sky

**adaws** · 01-04-2012, 08:25 AM

Hi SkyShield,
Because its a if function and the result is either true or false, you can give a conditional format based on the cell value, EG: if the cell value is equal to "Missing" then format it and if its not equal then a different format.

Thanks

**Skyshield2012** · 01-04-2012, 10:05 AM

Thank you Adaws.

Problem is that try to have this setup this way.

How can i solve it?

I am sitting on it since a few hours already and i dont see the light.

MODE OF TRANSPORT PGI LD ETD ETA BL(this one is a box where he has to request documents)
air 12/12/2011 04/01/2012

I want to have this set this way that when Air is selected as transport mode and the date is today that the BL box shows up red and white letters writting Missing

I am getting desperate hehehe :-)

Thanks for your help.

Sky

**Fotis1991** · 01-04-2012, 10:17 AM

can you upload a sample workbook??

**Skyshield2012** · 01-04-2012, 10:26 AM

Hi Fotis1991,

Thanks for your reply.

You'll see its quite a mess :-)
Never learned Excel and i have been challanged to do something now :-)

The excel is attached as you requested.

Thanks in advance.
Apreciate.

Sky

**Fotis1991** · 01-04-2012, 10:48 AM

Hi

B2:F4, IS THE RANGE THAT I USE cOND-foRM.

Is it, what are you looking for??

**adaws** · 01-04-2012, 10:38 AM

check the attached file

**Skyshield2012** · 01-04-2012, 10:48 AM

Hi Adaws.

Thanks for the excel.

That is the way it works but is there a way to make it work "automatically"?

I mean the way it is the colleagues needs to pull the formula downwards when he arrives at the end of the actual formulated cells.

Thats why i was searching for the other option directly over the Conditioned formatting.

Thanks again for your kind help.

Sky

**Skyshield2012** · 01-04-2012, 11:01 AM

Hi Fotis1991,

Not exactly.
difficult to explain when you are new to this.

The code i use is in L2.

I want that code to show the box red and white text writting Missing.

I have attached a picture.
Maybe that helps :-)

Thanks again.

Sky