**Question description:**

*This user has given permission to use the problem statement for this blog.*

conditional format required for cell if another cell is 100% and cell is less than 100%

Solved by A. S. in 11 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
16/07/2018 - 01:10

Hi i need a conditional format to turn cells B red if they are less than A when 100% and they are less then 100% themselves

Excelchat Expert
16/07/2018 - 01:10

Hello, I understand that you need help with conditional formatting.

User
16/07/2018 - 01:11

If A is 100% turn B red unless B is 100% then turn green

User
16/07/2018 - 01:11

if(and?

Excelchat Expert
16/07/2018 - 01:11

I see. Okay, Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.

Excelchat Expert
16/07/2018 - 01:12

Will you be able to provide an example using the document to the right?

Excelchat Expert
16/07/2018 - 01:12

I mean, if you could provide all possible situations, it would help me a lot.

Excelchat Expert
16/07/2018 - 01:12

How about the rest?

Excelchat Expert
16/07/2018 - 01:13

So A doesn't have to be 100%?

User
16/07/2018 - 01:13

I already have a conditional format for that but i am struggling on this one

User
16/07/2018 - 01:13

When A is 100%

Excelchat Expert
16/07/2018 - 01:14

Don't worry, I'm here to help. I'm just trying to fully understand what you need and what you already have implemented so I can provide an accurate solution.

Excelchat Expert
16/07/2018 - 01:14

So, let's start from scratch, give me the exact rule you need even though you already have the rule for when A is 100% so I can create one that works for you.

Excelchat Expert
16/07/2018 - 01:16

I see. Thank you. That's very helpful.

Excelchat Expert
16/07/2018 - 01:16

So these 4 are all the rules then?

User
16/07/2018 - 01:17

I have 3 rules i cannot get the 4th in cell 14 to work

User
16/07/2018 - 01:17

a14

Excelchat Expert
16/07/2018 - 01:17

Oh, so first 3 are all set, okay. One moment.

Excelchat Expert
16/07/2018 - 01:18

Based on how you worded your 4th rule, this should work:

Excelchat Expert
16/07/2018 - 01:18

=and(A2=100,B2<100)

User
16/07/2018 - 01:19

So i will have red amber green and also another red conditional format. 4 formats altogether right?

Excelchat Expert
16/07/2018 - 01:19

It depends on how you setup your priorities.

User
16/07/2018 - 01:20

I can move them up and down untill correct

Excelchat Expert
16/07/2018 - 01:20

Yep, that's all you need to do.

User
16/07/2018 - 01:20

Thank you so much

Excelchat Expert
16/07/2018 - 01:20

=and(A2=100,B2<100) simply checks A if it is 100, then checks B if it is less than 100.

User
16/07/2018 - 01:20

Do i close the page

Excelchat Expert
16/07/2018 - 01:20

Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.

Excelchat Expert
16/07/2018 - 01:20

I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.

Excelchat Expert
16/07/2018 - 01:21

Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*