Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Conditional Formatting Colour

0

Hello, I want to ask about conditional format rules. The conditions I want to apply are very simple but idk why this conditional function still not giving me the result that I'm looking for. So, I want to apply 3 conditions as bellow:

if the percentage movement > 0%, the cell has green round colour

if the percentage movement = 0%, the cell has yellow round colour

if the percentage movement < 0%, the cell has red round colour

my problem here i can't make cell containing percentage movement value that is "=" or equals to 0% has yellow colour. I'd like to know what mistake i put in the conditional formating rules here. I am waiting for your answer and thank you in advance.

excel
MS Office Version: Fida_mutia
posted by: Fida_mutia (rep: 28) on Fri Sep 17, 2021 at 1:38 pm
Answer
Discuss

Answers

0

Fida

I haven't worked out how to do that but see the revised version of your file where  conditional formatting is based on cell value but the fill is the result. There are three rules, on example (for cell C4) is:

=IF(C4=0,1)
with a chosen fill of yellow (so if the change is really 0, then the fill is yellow. I note that you were only displaying the % intger so I increased the displayed value to 2 decimal places (to prove the formula delivers the result if should).

All rules are extended in the "Applies to..." after creating/testing on cell C4.

Hope this helps.

posted by: John_Ru (rep: 6142) on Fri Sep 17, 2021 at 2:28 pm
Discuss

Discussion

oh i think cell E4 & F4 dont have yellow round there bcs their value is greater than 0.00% unlike G4 which shows exactly 0.00%.
Then what should i do if i want cell E4&F4 to also have yellow round even if their value is greater than 0.00% bcs i dont want decimal on my percentage movemnt cell so should i extend the conditon value like this " <=0,99 " or  " >=0,01 " ?? 
Fida_mutia (rep: 28) Sep 17, '21 at 3:08 pm
Fida. You can use AND in the If statement to get value ranges e.g. for yellow, edit the condition to read:
=IF(AND(C4>=.-0.5%, C4<=+0.5%),1)
(replacing my decimal place point with your comma).

Take care that your ranges don't overlap (in which case the order of rules will apply. 
John_Ru (rep: 6142) Sep 17, '21 at 3:49 pm
John, please review my new question with the same topic as this too. I decided to =round the total so that it meets my need but turned out theres still a cell containing value of 0% has down-pointing red arrow instead of sideways yellow arrow. I've attached the report which will use the conditional formatting function im working on. Some data in the report has been deleted bcs privacy and dont affect the calculation in the report.
Fida_mutia (rep: 28) Sep 19, '21 at 6:08 am
Add to Discussion
0

Hello Fida_mutia,

Because Excel computes using the actual cell value and not the displayed (rounded) value, conditional formatting has to consider the actual cell value not the displayed value.

EX: F4 actual cell value is -0.001197876197876 and not 0 as displayed.

I have attached your file, modified with CF rules applied to achieve the result you are looking for.

posted by: WillieD24 (rep: 557) on Sun Feb 6, 2022 at 11:36 am
Discuss

Discussion

Willie

As far as I recall, I answered Fida's later question (mentioned in the Discussion) successfully.

Your answer is largely correct but I think it has two blind spots (when the vaue is exactly 0.005 or -0.005). That can be fixed by making the limits the same but adding a set of equals sign at the boundaries e.g. (with changes in bold):
Cell value >0.005
formula: AND(C4>=-0.005, C4<=0.005)
Cell value <-0.005
John_Ru (rep: 6142) Feb 7, '22 at 3:54 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login