How do I apply formatting to a cell based on comparison with another cell?

Question

I'm working with Google's spreadsheets to keep a record of how many dragons of each breed/gender I have in a game I play. I have five columns:

A (Breed) | B (Male) | C (Male Goal) | D (Female) | E (Female Goal)

I want to highlight B with green if it is greater than or equal to C, red if it is less than C.

I want to highlight D with green if it is greater than or equal to E, red if it is less than E.

Obviously, I could use conditional formatting for each individual row to achieve this, but there's 116 rows so I was hoping there was a formula that would work for the entire column.

Answer 1

Here is what you can do:

1. RightClick B2 → select Conditional formatting → select Custom Formula is (from the List)
2. Enter the following formula in the textbox: =b2>=c2
3. Check the Background Color and select green color
4. Click on Add another rule
5. Select Custom Formula is (from the List)
6. Enter the following formula in the textbox: =b2<c2
7. Check the Background Color and select red color
8. Click on Save Rules
9. Highlight B2 and click on Paint Format option on the tool bar.
10. Now click on cell B3 and drag until cell - B117 (this should apply the conditional formatting on cell ranging from B3-B117)

And for your second condition (i.e., to highlight D with green if it is greater than or equal to E, red if it is less than E) - simply repeat the above steps by interchanging D2 for B2 and E2 for C2.

Answer 2

I think @Vembu's solution works but might be simplified in two ways:

1. Apply one of the colours as normal fill to both columns (or the required range for those columns). Then just use one CF rule (to overwrite that 'standard' formatting, when appropriate). All relevant cells in B and D are either one colour or the other, for which one CF rule is sufficient.

2. Copy the rule from one column to the other(B to D) with Paste special, Paste format only. (The rule is the same for Male and Female, just shifted across two columns). This can however be achieved more easily by selection of the applicable Range, which may avoid copying other formatting where that is not to be replicated.

So: select B2:B117 and D2:D117, fill red, Format, Conditional Formatting, Custom formula is =B2>=C2 select Background Colour Green and Range: (which should already be selected as such) B2:B117,D2:D117 (or to suit).

Answer 3

Google Sheets has updated and now it looks like this if you want conditionally format with red background if sum in two cells is different in 3rd cell: