conditional formating (Excel 97)

Topic

New Reply

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 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

Reply | Quote

Replies

I would use the following:

1-  =And(Min(A1:F1]0)

2-  =Min(A1:F1)>=40

3-  =Max(A1,C1,F1)=0

Reply | Quote

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

Reply | Quote

Like this?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Legare probably beat me to it, but I think this is what you want.

Reply | Quote

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 (1-100) , it should be red
==> if two cells fills , and the marks is (1-100), 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

Reply | Quote

What did I miss? I think that is what my last solution does.

Reply | Quote

Yup..you came almost perfect Legare..but you
still miss 1 thing..please refer to attachment.many thanks

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

before that..what i’m doing wrong with this sum formula..i want to total cell a1 and b1..the result is in cell c1..(SUM(A1:B1)..the problem is ..although when there is no data in cell a1 and b1..the c1 cell always show 0..how can I avoid this and when there is no data..cell C1 will show nothing..

Ok..back to our CF..
yup..i have to change the CF from marks to grades because of a few reason but the main reason is when there is 2 paper of single subject.let say history..there will paper 1 and paper 2.total of the marks will divide to 1.25 in order to get the total marks..let say this things happened on cell a1:d1 where cell a1 is for paper 1 marks..b1 for paper 2 and cell c1 is for the total marks..the maximum marks
for paper 1 is 40..same with paper 2..let say the student get each 20 marks in paper 1 and 2..so the whole row will turn to ..right..thats why i have to change it to grade..

1 more thing..please consider that i maybe have to expand the range..so please consider makes it the easier you can.so i can manipulate it again the future with different situation..not a demand..its a request from fellow friend..

regards

PS: please refer to attachment for more details

Reply | Quote

NiuB,

I think the simplest approach is color the cells red, and use conditional formatting to change to Blue or Grey as appropriate.

I attach the file with the CF based on grades, but the cells are formatted red.

I also simplified your VLOOKUP, and created a named range – “Gades”.

Andrew

Reply | Quote

If you used cells C2, E2 and G2 in the Conditional Formatting formula you already have, would that not do what you want?

=If(SUM(A1,B1)=0,””,SUM(A1,B1))

Reply | Quote

Hehe..what was I thinking..lol.missed that..now the problem solved..What can I say Andrew and Legare.except ZILLION and ZILLION THANKS to you guys….You guys are a genius..Its ok now and run smoothly..regards

Reply | Quote

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.

Reply | Quote

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

Reply | Quote