Conditional Formatting or VBA?

[Cheryl4g]

I have a scenario where I'm not sure what approach to use.

I have 4 criteria that can be either Green=3, Yellow=2 or Red=1
and I have 8 quarters from Jan 1 2011 to Dec 31 2012.

If the current date is 4/1/11 and the criteria is 3,3,3,3 then Q1 needs to be Green, ( or the blank cell below Q1, creating a color bar)
If the date is 4/1/11 and any of the criteria is a 2 then Q1 should be Yellow.
If any of the Criteria is a 1 then Q1 should be Red.

If the current date is 7/10/11 and the criteria is 3,2,3,1 then Q1 and Q2 should be Red.

What is the best way to tackle this logic?

I can get the color bar for the Quarters by using conditional formatting of =L5(cell of current date)> M5(last day of quarter 3/31/2011)

But I can't figure out how to include the logic for the criteria, to change the color to yellow or red if any of the criteria is a 2 or 1.

Any suggestions?

 

[asking]

You have not said which cells the criteria are in.

Let's say, dates to compare are in L5 and M5 and your criteria is in cells N5,O5,P5 and Q5
your conditional format formula would be ~ for Q1 to make yellow, if any criteria is 2

=AND(L5>M5,OR(N5=2,O5=2,P5=2,Q5=2))

You can use more than 1 rule for Q1, just need to put them in priority order.

In your green conditional format all your criteria needs to = 3
so the formula for that would be ~ assuming criteria is where I said.

=AND(L5>M5,N5=3,O5=3,P5=3,Q5=3)

Using AND and the OR in the correct sequences with the correct bracketing will do all you need for your different combinations.

Cheers.

 

[Cheryl4g]

Thank you- works perfectly