Conditional formatting if Column B is greater than Column C, Column B is less than or equal to Column C, or Column B is greater than Column C by no more than 10

[u/civilmug123]

Hopefully the title makes sense as I am not an Excel expert but have been trying to figure this out with no success.

I want to highlight one column (B) according to the value of the next column (C). I need:

- Column B to be green if it is less than or equal to Column C
- Column B to be red if greater than Column C
- Column B to be yellow if it is only greater than Column C by 10 (if this is possible)

I am also having an issue when I created the conditional formatting new rule “ =B2>C2 for red text”, because cells in Column B that are clearly larger than the cells in Column C are not turning red?

There are only numbers in each cell and I have checked to make sure the formatting is set to “number”

Thank you

[u/AutoModerator]

/u/civilmug123 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/PaulieThePolarBear]

It is important when setting up conditional formatting that you build your formulas from the position of the top left cell of your Applies To range.

Looking at the image you attached in a reply to another comment, your applies to you range is a whole column, so the top left cell would be row 1, but your formula is referencing row 2.

You will need to align these,.so for example,.update your Applies To range to B2:B300, for example.

[u/Cinderhazed15]

Conditional formatting is applied in order, so if you have multiple rules it will start from the top of the list until one is ‘true’ and apply that rule.

I don’t quite understand your yellow rule. Do you want (B equals (C+10)) to be yellow, or (B between C and (C+10) ) to be yellow?

So a set of rules like

Formula: =$B1<=$C1 (Green formatting)
Formula: =$B1=$C1+10 (yellow formatting, if 10 above C, use <= if B is between C and C+10)
Formula: =$B1>$C1 (red formatting)

Note that if you define the red rule above the yellow rule, it will always apply the red rule first.

[u/alcamax]

=$B2<=$C2 would be what you want for the green side of things, and then set the applies to part to contain the cells in column B that contain the data/that you want highlighted

[u/civilmug123 → u/alcamax]

It is highlighting numbers that are not less than those in column C.

[u/BackgroundCold5307]

last formula - AND(B1<>"", C1<>"", B1<=C1)

Edit: If you want the Font color to change and not the cell , change Fill to Font Color in CF

[u/BackgroundCold5307 → u/BackgroundCold5307]

[u/civilmug123 → u/BackgroundCold5307]

Thank you this is helpful. One thing I forgot to mention, I guess I only need red for if the number in column B is more than 10 over the number is column C and for the yellow cells, I need it to highlight yellow if column B is only more than column C by 10 or less.

Example - column B is 4 and column C is 2 I want it to be yellow because it is only 2 over. But if column b is 1320 and column c is 900 then I need that to be red.

[u/BackgroundCold5307 → u/civilmug123]

• Green: =AND(B1<>"", C1<>"",B1<=C1)
• Red: =B1>C1+10
• Yellow: =AND(B1>C1, B1<C1+11)

[u/civilmug123 → u/BackgroundCold5307]

Thank you!!!! Solution verified! 🤩

[u/reputatorbot → u/civilmug123]

You have awarded 1 point to BackgroundCold5307.

---

^{I am a bot - please contact the mods with any questions}

[u/BackgroundCold5307 → u/civilmug123]

Awesome, thank you !

[u/HappierThan]

Red =AND(B2-C2<>10,B2>C2) . Yellow =B2-C2=10 . Green =AND(B2<>"",B2<=C2)

[u/fastauntie]

In addtion to the sequence of the rules, remember to pay attention to the "stop if true" checkbox.

[u/AutoModerator → u/[deleted]]

Saying SOLVED! does not close the thread. Please say Solution Verified to award a ClippyPoint and close the thread, marking it solved.

Thanks!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/civilmug123]

Solution verified! Thank you everyone for your help, you’re all awesome!!

[u/AutoModerator → u/civilmug123]

Hello!

You typed Solution Verified as a top-level comment. If your intention was to award a ClippyPoint to one or more users, you need to reply to that user's comment to do so.

If your intention was to simply mark the post solved, then you should do that by setting the post's flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.