+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
Office Software Thread, Excel 2003 Conditional Formatting - Student Targets in Technical; oh, i see what you meant now....
  1. #16

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,009
    Thank Post
    680
    Thanked 1,398 Times in 1,158 Posts
    Rep Power
    353
    oh, i see what you meant now.

  2. #17


    Join Date
    Oct 2006
    Posts
    3,412
    Thank Post
    184
    Thanked 356 Times in 285 Posts
    Rep Power
    149
    Quote Originally Posted by LosOjos View Post
    I was trying to make it tidier, so that the each cell checks it's own value against the formula, but I think I'm making it more confusing in my head than it needs to be, the following should work:

    Code:
    Use each of these formulae as "Formula Is" formatting (the formulae presume that the first cell you're applying this to is B1 and the first target is in A1, you'll need to adjust these to suit your spreadsheet):
    
    Green formatted cell:
    =IF(left(A1,1)<left(B1,1),1,if(left(A1,1)>left(B1,1),0,if(right(A1,1)<right(B1,1),1,0)))
    
    Amber formatted cell:
    =IF(left(A1,1)=left(B1,1),if(right(A1,1)=right(B1,1),1,0),0)
    
    Red Formatted cell:
    =IF(left(A1,1)>left(B1,1),1,if(left(A1,1)<left(B1,1),0,if(right(A1,1)>right(B1,1),1,0)))
    let me know how you get on with that
    Thats looking good.

    One problem though, the lettering is working the wrong way round; b is better than a.


    Thanks for your help so far!

  3. #18

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,498
    Thank Post
    1,456
    Thanked 1,201 Times in 814 Posts
    Rep Power
    712
    Quote Originally Posted by j17sparky View Post
    b is better than a.
    That's the problem with these huge formulae, easy to lose track lol!

    Hopefully this should do it then:

    Code:
    Green formatted cell:
    =IF(left(A1,1)<left(B1,1),1,if(left(A1,1)>left(B1,1),0,if(right(A1,1)>right(B1,1),1,0)))
    
    Amber formatted cell:
    =IF(left(A1,1)=left(B1,1),if(right(A1,1)=right(B1,1),1,0),0)
    
    Red Formatted cell:
    =IF(left(A1,1)>left(B1,1),1,if(left(A1,1)<left(B1,1),0,if(right(A1,1)<right(B1,1),1,0)))

  4. Thanks to LosOjos from:

    j17sparky (21st June 2010)

  5. #19


    Join Date
    Oct 2006
    Posts
    3,412
    Thank Post
    184
    Thanked 356 Times in 285 Posts
    Rep Power
    149
    Brilliant, that works!

    It would also seem that you can copy down conditional formatting! Thats how long i havnt used excel for when 2003 has a feature i didnt know about!

    Thanks alot for your help, and everyone else. I do prefer this method rather than vLookups and vb as it should be abit more resiliant to teachers messing around with the worksheet.
    Last edited by j17sparky; 21st June 2010 at 01:53 PM.

SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

Similar Threads

  1. [MS Office - 2003] Conditional Formatting
    By garethedmondson in forum Office Software
    Replies: 1
    Last Post: 3rd February 2010, 01:40 AM
  2. [MS Office - 2003] Conditional Formatting
    By stevenwba in forum Office Software
    Replies: 3
    Last Post: 17th December 2009, 02:28 PM
  3. [MS Office - 2003] Excel - Conditional Formatting using formula
    By park_bench in forum Office Software
    Replies: 1
    Last Post: 10th June 2009, 02:25 PM
  4. Conditional formatting help needed
    By Jobos in forum How do you do....it?
    Replies: 3
    Last Post: 3rd June 2008, 01:58 PM
  5. Help with conditional formatting
    By randle in forum Windows
    Replies: 3
    Last Post: 25th October 2007, 01:20 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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