Question description:
This user has given permission to use the problem statement for this
blog.
Hello I am trying to highlight a cell one of two ways based on condition. So for example Cell C3 is the previous run time, and cell C4 is the most recent run time. I want cell C4 to highlight red if the value of C3 is less than C4, but I want the cell to highlight green if the value of C3 is greater than C4... how do i two this two level conditional formatting?
Solved by I. J. in 23 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/10/2018 - 04:43
Hello, I understand that you need help with highlighting a cell if it is greater than another cell and also highlight it differently if it's less than, right?
User
16/10/2018 - 04:43
yes!
Excelchat Expert
16/10/2018 - 04:44
I see. I can help you with this. 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/10/2018 - 04:45
Are you able to share your file so we can work on it?
User
16/10/2018 - 04:46
17-Oct 21-Oct 24-Oct 28-Oct
Wed Sun Wed Sun
Participant Goal Goal Goal Goal
Elizabeth G Run Time 0:13:00 0:12:52 0:12:51 0:12:43 0:00:00 0:00:00
Push Ups 28 31 32
Sit Ups 30 33 33
Ceara F Run Time 0:10:42 0:10:36 0:00:00 0:00:00 0:00:00
Push Ups 0
Sit Ups 0
i can do the push up and sit up part- it will be the opposite of what you show me, i am currently only concerned with the run time
Excelchat Expert
16/10/2018 - 04:47
Okay, the solution I'll be giving you will be using the exact format of the sample you provided.
Excelchat Expert
16/10/2018 - 04:48
So if your actual file is formatted differently or if the data is located in different rows and columns then you may have to adjust the solution I'll be giving you.
User
16/10/2018 - 04:48
thats fine
Excelchat Expert
16/10/2018 - 04:49
In your description C3 is the previous run time and C4 is the most recent run time. In the data you provided C3 is blank.
User
16/10/2018 - 04:49
also the columns labeled 'goal' dont need to be highlighted. thats where the new run time is calculated. just the adjacent column that shows the new run time. ie did you make the goal or not
Excelchat Expert
16/10/2018 - 04:49
Can you explain your requirement again and this time use the proper cells so I'll have a clear understanding of your requirement before I start with the solution?
User
16/10/2018 - 04:50
yes so basically when we look at E4, is it less or greater than the goal calculated in D4
Excelchat Expert
16/10/2018 - 04:50
E to D? Shouldn't we be comparing C to D?
Excelchat Expert
16/10/2018 - 04:51
We can make it work with E to D, I just want to be sure that I'm using the right columns for the formula.
User
16/10/2018 - 04:51
d and e go together then f and g and so on
User
16/10/2018 - 04:52
the goal is generated after you insert the first run time, so that becomes the target for the next run time
Excelchat Expert
16/10/2018 - 04:52
Okay, can you add a sample data for Oct 24 and Oct 28 as well?
User
16/10/2018 - 04:52
yes but im not sure my formula for the other works on this
User
16/10/2018 - 04:52
let me do it on my end and reupload it
Excelchat Expert
16/10/2018 - 04:53
We don't need formulas yet. Just sample times would be fine.
Excelchat Expert
16/10/2018 - 04:53
For instance Oct 21's runtime is 12:51 and the goal is 12:52 so it means you beat your goal so it will be highlighted in green, right?
User
16/10/2018 - 04:54
correct
Excelchat Expert
16/10/2018 - 04:54
And then in October 24 you ran 0:12:55 which is longer than the goal of 12:43, G4 would be red then?
User
16/10/2018 - 04:54
and if it is equal or more than (as on oct 24 and 28) then it needs to be red
User
16/10/2018 - 04:55
exactly
Excelchat Expert
16/10/2018 - 04:55
Ah, so equal is red as well. Thank you.
Excelchat Expert
16/10/2018 - 04:55
Alright, I got all that I need. I'll be working on this offline on my Excel and will need 5 to 10 minutes. Please extend the session when prompted.
User
16/10/2018 - 04:56
sure thing thanks
Excelchat Expert
16/10/2018 - 05:00
Nearly done.
User
16/10/2018 - 05:02
ok
Excelchat Expert
16/10/2018 - 05:03
Please download this file.
[Uploaded an Excel file]
Excelchat Expert
16/10/2018 - 05:03
You will see 2 sheets.
Excelchat Expert
16/10/2018 - 05:04
One is the sheet with your sample data and the conditional formatting already applied up to row 10 of columns E,G and I.
Excelchat Expert
16/10/2018 - 05:04
And the other sheet is called the INSTRUCTIONS sheets where I have placed the detailed steps on how to apply the conditional formatting.
User
16/10/2018 - 05:04
sweet thanks so much
Excelchat Expert
16/10/2018 - 05:04
The green colored instructions is for the Green highlighting and the red is for the condition where you don't make the goal.
User
16/10/2018 - 05:05
excellent thanks so much
Excelchat Expert
16/10/2018 - 05:05
Just adjust the rows and columns accordingly.
Excelchat Expert
16/10/2018 - 05:05
Would there be anything else that I can help you with regards to the original question?
User
16/10/2018 - 05:05
that was it
User
16/10/2018 - 05:05
thanks
Excelchat Expert
16/10/2018 - 05:05
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/10/2018 - 05:05
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/10/2018 - 05:05
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.
