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.
