Conditional Formatting Problem Solving
Forum - RPPF-CS-Dissolution Calculation Sheet for S1,S2 & S3-01.xlsxForum - RPPF-CS-Dissolution Calculation Sheet for S1,S2 & S3-01.xlsx
Can it be possible to make conditional formatting by using these logic -
Each unit should be more than 80% and
Not more than 2 units are less than 65% and
No unit is less than 55%
In your example, which region contains the percentage sign (%)?
Where would you like to apply color?
Please manually color the area you want to highlight.
Quang PT
Welcome to the forum.
What type of conditional formatting? Where should it appear? Please mock up manually what you expect it to look like.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sure. I am uploading it after correction
Here in my excel, i have coditions between H4 cell and H24 cell.
I want to make red color if any cell between G4 cell to G30 cell does not follow the condition of cell between H4 and H24.
Thank you bebo021999Forum - RPPF-CS-Dissolution Calculation Sheet for S1,S2 & S3-01.xlsx
There is no red highlighting that has been added - please add it MANUALLY where you expect to see it so that we can creat a CF rule for you.
So, for the range G4:G27, set default color to RED.
Then, there are 3 criteria to turn from RED to other color:
1) "No unit is less than 55": should be the min >55:
G29>=H23
2)"Not more than 2 units are less than 65"
COUNTIF($G$4:$G$27,"<65")<=2
3) Each unit should be more than 80: total unit = 24, minus 2 units can be <65, the last 22 units must be greater than 80
COUNTIF($G$4:$G$27,">80")>=22
combination of three criteria:
set color to "No color" or gray
Please Login or Register to view this content.
Thanks a lot bebo021999.
I need one more solution please. If you check this, it would be a great relief for me. I changed the conditional formatting color from the file you provided me. But all the G4:G27 cell are being shown red. But it needs to change color only those cell which follow the "=AND($G$29>=$H$23,COUNTIF($G$4:$G$27,"<65")<=2,COUNTIF($G$4:$G$27,">80")>=22) "Excelforum - RPPF-CS-Dissolution Calculation Sheet for S1,S2 & S3-01 (1).xlsx
No, you are set color to red, both: default and conditional formating.
Set grey (or other color other than red) for conditional formating.
Last edited by AliGW; 11-28-2023 at 04:37 AM. Reason: Please do NOT quote unnecessarily! Use the QUICK REPLY button instead.
But all the G4:G27 cell are being shown grey. But it needs to change color only those cell which follow the "=AND($G$29>=$H$23,COUNTIF($G$4:$G$27,"<65")<=2,COUNTIF($G$4:$G$27,">80")>=22) " not all the cells
Actually i should be like this. All the G4:G27 cell are being shown grey. But it needs to change color only those cell which follow the "=AND($G$29>=$H$23,COUNTIF($G$4:$G$27,"<65")<=2,COUNTIF($G$4:$G$27,">80")>=22) " not all the cells Attachment 850784
You should make a sample, to color cells with BOTH, grey AND red color.
2_Excelforum - RPPF-CS-Dissolution Calculation Sheet for S1,S2 & S3-01 (1).xlsxHere it is bebo021999
Thanks for your support. I want to change those cell color red which does follow "=AND($G$29>=$H$23,COUNTIF($G$4:$G$27,"<65")<=2,COUNTIF($G$4:$G$27,">80")>=22) " and other cell will remain gray color.
Cf rule for G4:
=OR($G4<80,COUNTIF($G$4:$G$27,"<"&65)>2,COUNTIF($G$4:$G$27,"<"&55)>=1)
Applies to: =$G$4:$G$27
Any good???
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Still its not working. I am checking the excel. I do not understand why its not working properly
So no 'thanks for your help', then?
It is working in my attachment. Did you look at that? Maybe you didn't copy the formula correctly. I cannot tell you why it's not working correctly unless you show me an example of it not working.
The attached has the other cells in grey - is this what was 'wrong'?
If not, I have no clue what 'not working properly' means.
Thanks a lot for your support.
Here in H12 cell i changed the value to 80. The previous value is 59 from the file you provided me. Then i changed the value of 59 to 80. So when i put 80, then see G4:G27 , i have only 2 values which follows the condition from the limit column. But nevertheless it shows color in G16 and G20 cell from conditional formatting. But that was not going to happen. Cause only 2 values are less then 65. It should colored the cell from conditional formatting when more than 2 unit are less then 65.
In the limit column it says - "Not more than 2 units are less than 65 and No unit is less than 55" .
I have attached the sample in screenshotAttachment 850800
So just change the rule to this:
=OR($G4<=80,COUNTIF($G$4:$G$27,"<"&65)>2,COUNTIF($G$4:$G$27,"<"&55)>=1)
Last edited by AliGW; 11-28-2023 at 05:07 AM.
Re: Conditional Formatting Problem Solving
I am giving you two screen shots.
Here in G11, G16, G20 cells values are 80, 65, 63 from the last excel you provided me. Here its showing about red color white font. But that does not supposed to happen. Cause it follows only 2 values are less then 65.
789.PNG
But it was supposed to happen like this cause only 2 units are less then 65 159.PNG
But when more then 2 units are less then 65 then it would show red color white font color. 357.PNG
Forget the grey shading for now, just focus on the red.
Does this do what you want?
=OR(AND($G4<80,COUNTIF($G$4:$G$27,"<"&65)>2),AND($G4<80,COUNTIF($G$4:$G$27,"<"&55)>=1))
Thanks a lot AliGW . Its working now <3 . Bothered you a lot. You two guys helped a lot. Too much grateful to you.
Attached with grey fill also applied.
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
I already marked this thread as SOLVED.
Can you go through my another thread? No one is responsing.
https://www.excelforum.com/excel-for...ific-cell.html
I can't help you with the other query. Sorry.
Please be patient. This isn't a paid service and we are all volunteers.
Sure, its okay. What you helped for that i am too much grateful to you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks