Google Sheets Conditional Formatting of another cell based on variable values

Question

I have rows that include various totals of either 5, 10 or 12. I also have checkboxes that add up to a total of 12, and a cell value for each row that calculates the difference to reveal how many are left.

I'm wanting the last result cells to show colours from red to yellow to green that is independently reliant on the first cells value to show a specific shade of green/yellow/red. I'm only able to select a colour based on the single columns/cells value and not the value of another column/cell. Is this even possible?

I've run through a number of examples given on google and answers based on similar questions, but nothing seems to solve this specific situation.

This is what I'm expecting to happen: Green Yellow Red

For Green Total:

Row 0:  Column A:         | Column B:     | Column C:

Row 1:  Classes Available | Classes Left  | Colour

Row 2:  10                | 10            | Green

Row 3:  5                 | 5             | Green

Row 4:  12                | 12            | Green

For Variable Total:

Row 0:  Column A:         | Column B:     | Column C:

Row 1:  Classes Available | Classes Left  | Colour

Row 2:  10                | 5             | Yellow

Row 3:  5                 | 4             | Green

Row 4:  12                | 3             | Red

For Red Total:

Row 0:  Column A:         | Column B:     | Column C:

Row 1:  Classes Available | Classes Left  | Colour

Row 2:  10                | 0             | Red

Row 3:  5                 | 1             | Red

Row 4:  12                | 2             | Red

Basically: If we're looking at 3 cells next to each other in ABC, then if B is the variable, and A is the total value, then C should show the colour based on how equal B is to A for Green, or how much lower the value of B is to A for Yellow to Red.

Answer 1

You can simply quote cells in conditional formatting formulae. For example, you could have cell C2 format based on the equation:

=A2>B2

I think, based on what you've given, you want to scale it based on how much bigger the first is to the second. ie: if A is much bigger than B, turn C red but if A=B, turn C green. If that's the case then you can do colour scales with:

Minpoint: = 0    Red
Midpoint: = A2/2 Yellow
Maxpoint: = A2   Green

And then put

=B2

in C2.

Answer 2

You can use cell references in your conditional formatting formulas. Try using custom formulas for more flexibility.