Creating code or conditional formatting to change colour of cell based on values in other cells

[ashleynh2011]

Hi there, I'm trying to figure out how to make a cell turn a different colour based in column A and row B - is this possible? Here's the visual I'd like to automate

 

[Joe4]

Welcome to the Board!

I think we are going to need a bit more of an explanation. Can you walk us a simple example, in detail, explaining what cells should be colored based on what?

 

[braindiesel]

Give me a bit more info and I will be happy to help as best I can.

Which specific cell do you want to be the trigger?
Which specific cellsshould change as a result?

 

[ashleynh2011]

Thank you! my apologies...looking back, my post was incredibly vague! Participants are all allocated to a cohort and there are 3 current cohorts at a time who each spend 3 years in the program. Once they complete their 3 years, they are considered alumni. What I wanted to do is completely automate this table which is pulling from another tab which has all this raw data extract from the system. Where you see the total alumni and total current participant lines, I've created a 'SumByColour' function, but now what I want to do is automate how those boxes are coloured. So basically in the table, if the cell is white, they are current participants and if it's grey, they're alumni. The numbers are the top of the image "newest cohort" and "oldest cohort" means that (in the first column) cohorts 3-5 are current participants. I want the cell colour to change to either grey or white for the entire table (organized quarterly by year) based on whether each cell is a current participant or alumni. Does that make sense?

 

[braindiesel]

Hmm... ok... lets's give this a shot...
Assuming in order like this (Alumni listed first)
Assuming the cell for the Total Alumni Q1 (24 value) is in cell C5 and the first 12 in Q1 is in cell C7...

Conditional format in cell C7
New rule
Based on a formula
=sum(C$7:C7)<=C$5
format the gray colour
Copy your formatting to the other cells... should solve it

 

[ashleynh2011]

You are my hero, thank you!! The only trouble is, it didn't work for the second table (which I hadn't told you about in my initial post, so my fault). It's the same type of concept, so I thought it would work for the terminations table. Do you know what I would need to change to make it work for that table as well? Here's a more detailed snapshot.

 

[braindiesel]

How is this in cell Z11's conditional formatting?
=AND(Z$9>0,OR(SUM(Z$11:Z11)<Z$9,AND(SUM(Z$11:Z11)=Z$9,Z11>0))