Results 1 to 15 of 19
Thread: conditional formating (Excel 97)

20010713, 16:34 #1
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
conditional formating (Excel 97)
Hello again..hope you guys can help me again
with this headache conditional formating
I have set a range between a1:f1
1. when there is marks under 39 and below in either 1 of the range (a1,b1,c1,d1,e1,f1) ,
it will turn to red (any one from the cell filled is enuff to turn it to red)
2. if there is 40 and above marks in range a1:f1 (all cell from a1:f1 must be filled with 40 or above marks), it will turn to blue
3. if range a1:f1 is empty, it will turn to grey (cell b2, d2 and f2 are set with vlookup formula)
(cell a1,c1,e1 is for user to keyin their marks)
i have succeeded with no.1 and no.2..my problem
is with no.3..when this is no marks in cell a1,c1 and e1..coz it turn to red..i need it to turn to grey..
hope you guys understand my question and can help me...many thanks
1. turn to red = OR(A1:F1<40) ==> i can do this
2. turn to blue = greater than 40 ==> i can do this
3. turn to grey = when there's nothing ==> this my problem

20010713, 16:49 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
I would use the following:
<pre>1 =And(Min(A1:F1]<40,Max(A1:F1)>0)
2 =Min(A1:F1)>=40
3 =Max(A1,C1,F1)=0
</pre>
Legare Coleman

20010713, 17:32 #3
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Thanks for the response Legare..but I think you
dont understand my real question..I'm also getting headache trying to explain this situation.Please refer to my attachment for details.Many thanks..and hope you can help me again and again..LOL..regards

20010713, 17:54 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: conditional formating (Excel 97)
Like this?
John ... I float in liquid gardens
UTC 7ąDS

20010713, 17:59 #5
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Thanks and Sorry John..i've got all messed up..the truth is the wb that i sent earlier is not an update
version of what i really need..but you response too fast..sorry to get you also in this mess..this is really what i am for..haha..i'm really screwed up by this situation..please refer to this attachment..this time its for real

20010713, 19:01 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Now I really don't understand what you want to do:
1 What is the difference between "no marks entered" and empty?
2 What color do you want it to be if one is empty, one is below 40, and one is above 40?
I took a guess, see if the attached sheet does what you want.Legare Coleman

20010713, 19:24 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: conditional formating (Excel 97)
Legare probably beat me to it, but I think this is what you want.
John ... I float in liquid gardens
UTC 7ąDS

20010714, 00:42 #8
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Thanks for the response again..but you guys still miss 1 thing..
So in order to answer legare question..i try to elaborate it.the details that i can..lol..as i said b4..i'm also got headache because of this..
1. What is the difference between "no marks entered" and empty?
==> if no cells fill with marks at all=empty (a1,c1e1), it should be grey
==> if either one cells fills(one single cell is enuff) , and the marks (1100) , it should be red
==> if two cells fills , and the marks is (1100), it also should be red
==> if three cells fills , and the marks is equal to 40 and above, it should be blue
ps:
* the student must get the minimum marks=40(a1,c1,e1) in all subjects in order to get pass=blue(whole range)
* if they miss 1 subject(fail)=below40(a1,c1,e1) in any subject, they will get fail=red(whole range)
* if no student marks entered=empty , it turn to grey(whole range)
So the conclusion is : student must pass all subject(a1,c1,e1) to get =blue
i hope you guys understand now and can help me again to solve this saga..
2 What color do you want it to be if one is empty, one is below 40, and one is above 40?
the answer is red

20010714, 01:28 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
What did I miss? I think that is what my last solution does.
Legare Coleman

20010714, 03:24 #10
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Yup..you came almost perfect Legare..but you
still miss 1 thing..please refer to attachment.many thanks

20010714, 11:38 #11
 Join Date
 Feb 2001
 Location
 Dublin, Ireland, Republic of
 Posts
 2,697
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Niub,
As you only use 3 colors, you can color all the cells grey and then use the following conditional formatting :
Condition 1 : Formula Is =AND(OR(OR($A1<40,$C1<40),$E1<40),$A1&$C1&$E1<>"")
Condition 2 : Formula Is =AND(AND($A1>=40,$C1>=40),$E1>=40)
If you do not want to color the cells grey, you need to add the following format :
Condition 3 : Formula Is =$A1&$C1&$E1=""
Workbook attached
Andrew

20010714, 12:48 #12
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Finally its working..what a relief..Thanks a zillion Andrew coz solved this..but I'm still wondering..if this method can also applied basedon grades..please refer to attachment..many thanks again

20010714, 13:23 #13
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
That appears to be a bug in Excel. If I put =MIN(A3,C3,E3) in a cell it returns zero if the cells are null, 40, 40. However, that same formula in the conditional formatting appears to return 40.
I see that Andrew has already sent you a solution that seems to work. However, I have attached my solution fixed to get around the apparent Excel bug.Legare Coleman

20010714, 13:26 #14
 Join Date
 Jul 2001
 Location
 Kuala Lumpur , MALAYSIA
 Posts
 154
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
Thanks again Legare for the hard work..want a new chalenge.look above about my new post about
CF based on grades.and the attachment.reagrds

20010714, 13:48 #15
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional formating (Excel 97)
I have fixed your VLOOKUP formula in the attached sheet.
Why do you want to change the Conditional Formatting. What you have seems to already be doing what you want. The formula probably can be changed to use the letter grade, but it is going to make the formula VERY complex.Legare Coleman