Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    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

  2. #2
    Uranium Lounger
    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

  3. #3
    2 Star Lounger
    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
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: conditional formating (Excel 97)

    Like this?
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    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
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: conditional formating (Excel 97)

    Legare probably beat me to it, but I think this is what you want.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    2 Star Lounger
    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 (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

  9. #9
    Uranium Lounger
    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

  10. #10
    2 Star Lounger
    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
    Attached Files Attached Files

  11. #11
    Gold Lounger
    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
    Attached Files Attached Files

  12. #12
    2 Star Lounger
    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
    Attached Files Attached Files

  13. #13
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

  14. #14
    2 Star Lounger
    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

  15. #15
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •