**Question description:**

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

I need to pout conditional formatting for a scenario where if the cell value is positive then green and red if the cell value is negative, however, i also need to give refernce to other cell where another value basis which i need to validate before giving the cell a red color

Solved by O. U. in 28 mins

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

Excelchat Expert
06/09/2018 - 07:56

Hello

Excelchat Expert
06/09/2018 - 07:56

Welcome

User
06/09/2018 - 07:56

Thanks

User
06/09/2018 - 07:56

need to pout conditional formatting for a scenario where if the cell value is positive then green and red if the cell value is negative, however, i also need to give refernce to other cell where another value basis which i need to validate before giving the cell a red color

Excelchat Expert
06/09/2018 - 07:57

I understand. Please tell me all the things along with sending the file if possible.

User
06/09/2018 - 07:57

i ahve already told the requirement

User
06/09/2018 - 07:58

i just want to know the solution, m not sending any filke

Excelchat Expert
06/09/2018 - 07:58

I understand. What I need more is about this part. "however, i also need to give refernce to other cell where another value basis which i need to validate before giving the cell a red color"

User
06/09/2018 - 07:58

cell a contains a value in %age

Excelchat Expert
06/09/2018 - 07:59

It is hard to put out a solution without example data. So I would be needing some example too.

User
06/09/2018 - 07:59

cell b is the cell where i need to put conditional formatting

User
06/09/2018 - 08:00

okay sharing an exapmple

User
06/09/2018 - 08:00

cell a is the cell where i neef to conditional format

Excelchat Expert
06/09/2018 - 08:00

Thank you. I'm waiting for it.

User
06/09/2018 - 08:01

if the cell value is negative then red else green

Excelchat Expert
06/09/2018 - 08:01

Please show this on the blank spreadsheet on the right hand side.

User
06/09/2018 - 08:02

now conditional formatting for cell b2 needs to be done,

User
06/09/2018 - 08:02

red for negative and green for positive

Excelchat Expert
06/09/2018 - 08:02

Anything else?

User
06/09/2018 - 08:03

however, for a red i also need to check if the value in c 3> 3%

User
06/09/2018 - 08:03

if yes then red

User
06/09/2018 - 08:03

else no color

User
06/09/2018 - 08:03

if it is a poitive number then green irrespective of the value in cell c3

User
06/09/2018 - 08:03

hope i am able to explain now

Excelchat Expert
06/09/2018 - 08:04

SO, red if B2 is negative and B3 is greater than 3%, green if B2 is positive, otherwise no color, right?

User
06/09/2018 - 08:04

yes

Excelchat Expert
06/09/2018 - 08:04

Yeah, the explanation was great. You made it crystal clear, thank you.

User
06/09/2018 - 08:04

Thanks

Excelchat Expert
06/09/2018 - 08:04

What if B2 is 0?

User
06/09/2018 - 08:04

no color again

Excelchat Expert
06/09/2018 - 08:05

Alright. We need two different rule. First we will write the rule for positive number.

User
06/09/2018 - 08:05

Ok

User
06/09/2018 - 08:06

you there?

Excelchat Expert
06/09/2018 - 08:06

1. Select B2, then click on the conditional formatting->new rule->Use a formula to determine....

Excelchat Expert
06/09/2018 - 08:06

Yeah, I writing the steps and need time to write, please stay with me.

User
06/09/2018 - 08:06

Sure

Excelchat Expert
06/09/2018 - 08:07

Here write the formula =B2>0 and then click on format to select color. Finally click OK to finish.

User
06/09/2018 - 08:07

Got it

Excelchat Expert
06/09/2018 - 08:07

Please remember the instructions I am giving is applied to Microsoft Excel.

User
06/09/2018 - 08:08

Sure

Excelchat Expert
06/09/2018 - 08:08

Now, we will follow the same steps for the negative numbers rule, but the formula here should be different.

User
06/09/2018 - 08:08

Ok

User
06/09/2018 - 08:09

Let me know the formula please

Excelchat Expert
06/09/2018 - 08:09

=AND($G$14<0,$G$15>3%)

Excelchat Expert
06/09/2018 - 08:09

Here is the formula for it.

Excelchat Expert
06/09/2018 - 08:09

Please test it and let me know if you have any question.

Excelchat Expert
06/09/2018 - 08:12

=AND($B$2<0,$B$3>3%) is the formula if we reference to the examaple sheet.

User
06/09/2018 - 08:15

I tried with this, however, it is red irrespective of the value in b3

User
06/09/2018 - 08:15

can you show me a demo please

Excelchat Expert
06/09/2018 - 08:15

Sure, sending the file.

User
06/09/2018 - 08:15

Thanks

Excelchat Expert
06/09/2018 - 08:16

[Uploaded an Excel file]

Excelchat Expert
06/09/2018 - 08:17

Please download and check the file.

User
06/09/2018 - 08:17

Allow me some time please

User
06/09/2018 - 08:17

i am checking

Excelchat Expert
06/09/2018 - 08:17

You will find the conditional formatting rules applied to cell B2 by selecting the cell and then clicking on conditional formatting->manage rules.

User
06/09/2018 - 08:18

yes

User
06/09/2018 - 08:18

also, if i want to apply this condition to various cells then how to do it

Excelchat Expert
06/09/2018 - 08:19

It depends.

Excelchat Expert
06/09/2018 - 08:19

First I need to know the exact "various cells" you are referencing to.

Excelchat Expert
06/09/2018 - 08:20

As always it is better understood with examples.

User
06/09/2018 - 08:20

Sure,

User
06/09/2018 - 08:20

so the same rule needs to be applicable on b2 - b30 for example

User
06/09/2018 - 08:21

i need to prepare a monthly sort of table where this rule needs to be applicable for each day

Excelchat Expert
06/09/2018 - 08:21

Where does B3 fits in this example?

User
06/09/2018 - 08:21

my bad

User
06/09/2018 - 08:21

b2, c2, d2

User
06/09/2018 - 08:21

and so on

Excelchat Expert
06/09/2018 - 08:22

Alright. You can just select the cells where you want to apply the same formatting and write the rule.

User
06/09/2018 - 08:23

so while applying the rule i need to select all teh cells simultaneously

Excelchat Expert
06/09/2018 - 08:23

For example in this case, select B2:D2 and write the rule =B2>0 for green and =AND(B2<0,B2>3%) for red.

User
06/09/2018 - 08:23

ok

Excelchat Expert
06/09/2018 - 08:23

Yeah. simultaneously.

User
06/09/2018 - 08:23

Thanks for the help

User
06/09/2018 - 08:23

appreciated

Excelchat Expert
06/09/2018 - 08:23

I'm glad that approve. :)

Excelchat Expert
06/09/2018 - 08:24

It would be nice if you can leave a good feedback rating for me.

User
06/09/2018 - 08:24

definitely

User
06/09/2018 - 08:24

will you be sending a link

Excelchat Expert
06/09/2018 - 08:24

You can leave a feedback after clicking on the END button to close this chat.

User
06/09/2018 - 08:24

ok

User
06/09/2018 - 08:24

will do that

Excelchat Expert
06/09/2018 - 08:24

Thank you, have a great day ahead!

User
06/09/2018 - 08:24

you too

**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.*