Multiple Criteria Conditional Formatting Query

[NewOrderFac33]

Good morning,
I have a project plan in Excel where cells in the plot area contain a formula returning a value of 1,2,or 3, with 3 conditional formatting rules resulting in the cell being colored differently e.g.
Cell Value = 1 - Cell Fill = Red
Cell Value = 2 - Cell Fill = Blue
Cell Value = 3 - Cell Fill = Green

I now need to extend these three rules to include the value in another column, so it would be something like:
Cell Value = 1 AND the value of (Cell.Row, Column B) = "Sprint1" - Cell Fill = Red
Cell Value = 1 AND the value of (Cell.Row, Column B) = "Sprint1" - Cell Fill = Blue
Cell Value = 1 AND the value of (Cell.Row, Column B) = "Sprint1" - Cell Fill = Green

Is this possible - I'm using 2010, by the way?

Thanks in advance
Pete

 

[NewOrderFac33]

I thought that this might have worked:

=AND($D14="Sprint1",CELL("contents",GA14)=1)

but I need the $D14 and GA14 bits to be relative, row-wise and column-wise when I copy the formatting down and across into the rest of the plot area cells and they aren't.
Almost there, but not quite..!
Pete

 

[Peter_SSs]

it would be something like:
Cell Value = 1 AND the value of (Cell.Row, Column B) = "Sprint1" - Cell Fill = Red

I thought that this might have worked:

=AND($D14="Sprint1",CELL("contents",GA14)=1)

Have we changed from column B to column D for looking for "Sprint1" or is one of them a typo?

but I need the $D14 ...to be relative ... column-wise

Are you sure of that? That is, could the "Sprint1" turn up in columns E, F, G etc?

 

[NewOrderFac33]

Good morning, Peter - - thanks for taking the time to reply - yes it WAS a typo. And - it actually works too - here are my 3 rules for each of "Sprint 1", "Sprint 2" and "Sprint 3"

=AND($D14="Sprint3",CELL("contents",GA14)=3)
=AND($D14="Sprint3",CELL("contents",GA14)=2)
=AND($D14="Sprint3",CELL("contents",GA14)=1)

=AND($D14="Sprint2",CELL("contents",GA14)=3)
=AND($D14="Sprint2",CELL("contents",GA14)=2)
=AND($D14="Sprint2",CELL("contents",GA14)=1)

=AND($D14="Sprint1",CELL("contents",GA14)=3)
=AND($D14="Sprint1",CELL("contents",GA14)=2)
=AND($D14="Sprint1",CELL("contents",GA14)=1)

My mistake was that I hadn't redefined the "Applied to" value to refer to the whole of my plot area!

Apologies for wasting your time.

Pete

 

[Peter_SSs]

OK, now wouldn't the simpler =AND($D14="Sprint3",GA14=3) also do the job?

 

[NewOrderFac33]

You're right - it does - what WAS I thinking?
Thanks, Peter!