Multiple Criteria Conditional Formatting Query

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,283
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I thought that this might have worked:
Code:
=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
 
Last edited:
Upvote 0
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:
Rich (BB code):
=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?
 
Upvote 0
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
 
Upvote 0
OK, now wouldn't the simpler =AND($D14="Sprint3",GA14=3) also do the job?
 
Upvote 0
You're right - it does - what WAS I thinking?
Thanks, Peter!
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top