Conditional Formatting using Formula

[FerrariWA]

Hello,

I am trying to create a formula within excel 2003 which will format a cell
depending on whether the criteria is met.
So for example, I want to use cell a4, that contains a date value to
calculate length in months using todays date then dependent on that outcome,
turn cell a10 red, if the length of months is 0-2 and value of cell a10 is
more than 240. if length is 3 months then turn red if more than 200 and if
length is greater than 3 months, turn red if more than 190 and so on..

Im thinking this requires some VB scripting to work?

There could be an easier way to do this.
Thanks,
WA

 

[Luke M]

Select A10. Under conditional formatting, first conditions, change first box
to "formula is:". Input this
=OR(AND(DATEDIF($A$4,TODAY(),"m")<3,A10>240),AND(DATEDIF($A$4,TODAY(),"m")=3,A10>200),AND(DATEDIF($A$4,TODAY(),"m")>3,A10>190))
Format with red pattern.

Note that this breaks down into 3 OR functions (rewritten for clarity):
=OR(
AND(DATEDIF($A$4,TODAY(),"m")<3,A10>240),
AND(DATEDIF($A$4,TODAY(),"m")=3,A10>200),
AND(DATEDIF($A$4,TODAY(),"m")>3,A10>190)
)

 

[Jim Thomlinson]

This does not require VB so long as we can develope one formula that returns
true or false based on the criteria being met.

We need to set up a table of your criteria... I created this in Cells D1:E12.
30 240
60 240
90 200
120 190
150 180
180 170
210 160
240 150
270 140
300 130
330 120
360 110


In Cell A10 add the following conditonal format
Format -> Conditonal Format... Formulas is
=A10>INDEX($E$2:$E$12,MATCH(A4-TODAY(),$D$1:$D$12,1))
Select your pattern to be Red
Select OK

 

[FerrariWA]

Hello,

Thanks for your help however I forgot to add that I want to change the cell
to a more colors if a different set of criteria is met.
So if it length in months was 0-2 months and value of cell a10 is less than
240 then amber and if length is 0-2 months again and a10 is under 200 then
green?

 

[Luke M]

Note that the formula I gave is just for condition 1. You still have 2 other
conditions for which you could create similar formulas. (note that with your
example, green will never happen, as anything less than 200 is also less than
240)

Jim Thomlinson has also provided a suggestion for dealing with long lists of
possible criteria.

 

[FerrariWA]

Luke,

Thanks for your help so far.
You are right, I was just using random data.

Does excel have the ability to use a between function? I am trying to
finish the formula off so just that bit left to do:

=OR(AND(DATEDIF($C$7,TODAY(),"m")<3,M7>240),
AND(DATEDIF($C$7,TODAY(),"m")>3,M7>203),AND(DATEDIF($C$7,TODAY(),"m")>3,M7>190))

The second IF criteria needs to be between 4-6 months.

Thanks,
WA

 

[Luke M]

There is not a specific "between" function, as it is created using the
structure:
AND(x>=0,x<=5)
This structure checks for x between (inclusive) 0 and 5.

Trying to guess what your going for, add an additional conditional in the
AND functions I listed, such as:
AND(...,M7>240,M7<270)