+ Reply to Thread
Results 1 to 12 of 12

advanced conditional formatting based on text value list

  1. #1
    Registered User
    Join Date
    11-20-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question advanced conditional formatting based on text value list

    Hello,

    Can conditional formatting or a programmed expression with excel be used to achieve this? (if the answer is yes, with a few pointers given, I can get my teeth into it)

    A spreadsheet is used to stored the skill level of pupils for a subject.

    Skill levels are as such:

    1C
    1B
    1A

    2C
    2B
    2A

    3C
    3B
    3A

    and so on, up to 5C, 5B, 5A.

    I need to setup excel so that it automatically colour codes the background of a cell to indicate if progress has been made. For example, in the Autumn term for maths, a pupil was skill level 1B. In Spring term they were 1A. (so they have gone up in skill, deamed by the test). In this instance, I would like the cell with 1A in it to be green, to indicate visually that progress has been made. If however they had gone backward to 1C, I would like this cell with 1C shown, to be red.

    EG:

    Autumn Spring Summer
    1B 1A 1B


    Can this be done? (If not, can it be achieve in a roundabout way?)

    Many thanks!

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,449

    Re: advanced conditional formatting based on text value list

    Set up a list of all possible skill levels in order, from lowest to highest. Give that list a named range, let's call it SkillLevels.

    Set conditional format for two rules using formulas. Assume Autumn, Spring, Summer are in A, B, C. Apply condition to the range B2:Cx where x is the highest row:

    =MATCH(B2,SkillLevels,0)<MATCH(A2,SkillLevels,0)
    Formatting: Red text

    =MATCH(B2,SkillLevels,0)>MATCH(A2,SkillLevels,0)
    Formatting: Green text
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: advanced conditional formatting based on text value list

    Can't be done in 2003 with more than 3 colors. It needs a macro to permanently change the color.

    Name the range containing the Pupil's Names "Pupils".
    Name the list with the colors to be assigned "LevelColors". Set the color of each cell in that list to the color you want the Pupils to be colored.
    Assuming the Skill Levels are in column D;
    In a column not visible (Say "Z") Z1: "=Match(D1,LevelColors,0)" copied down the column.
    Name the range in Column Z "LevelRows"


    Please Login or Register  to view this content.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,449

    Re: advanced conditional formatting based on text value list

    Quote Originally Posted by foxguy View Post
    Can't be done in 2003 with more than 3 colors.
    True, but it sounds like the request is just for two colors....

  5. #5
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: advanced conditional formatting based on text value list

    Quote Originally Posted by 6StringJazzer View Post
    True, but it sounds like the request is just for two colors....
    You're right. I didn't read all the details and I could have sworn I saw 3 colors in his/her example.

    EG: In case you do want more than 2 colors. Replace "Interior.ColorIndex" to "Font.ColorIndex". I was changing the background color, not the font color.

  6. #6
    Registered User
    Join Date
    11-20-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    5

    Exclamation Re: advanced conditional formatting based on text value list

    Thanks for all your help and advice, I look forward to getting a moment (!) to test this out.

    ...in reality I need about 5 colours.

    red = backward in attainment
    yellow = no movement
    green = forward one sublevel
    violet = forward two sublevels
    pink = forward three sublevels.

    eg 2B 2C 2A

    It is very unlikely for attainment trends to move out of those realms.

    Thank you all again, I've been meaning to get on here to reply my thanks!

    ...

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,449

    Re: advanced conditional formatting based on text value list

    If you need more than 3 rules in Excel 2003 then you need a macro solution like foxguy's. I would also put that in
    Please Login or Register  to view this content.
    so it is updated every time a value changes.

  8. #8
    Registered User
    Join Date
    11-20-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    5

    Lightbulb Re: advanced conditional formatting based on text value list

    I would like to thank you both for your advice on this. I've finally had some free time to work my way through it, and came across a few of my own inexperienced stumbling blocks with it. I look forward to getting better with this coding and fixing it to be more sophisticated to do as you've suggested above.

    As an initial starter, and stepping backwards, could you let me know the (surely simple) code to achieve the following? Ie, changing a cell text colour based on another cells value across range

    The sheet contains two columns of data. Pupil Progress.

    The progress states how many steps forward or backward the pupil is making in their academic performance. For example, progress could be -2 -1 0 1 2 3 4

    I would like the pupil's name to be coloured red if their progress is any negative number. And lets say Green (for progress of 1) Blue (2) Pink (3) Purple (4).

    I'm guessing some macro (for Access 2003 compatible) like this:

    If (progress cell value = 2) then (cell in same row from the Pupil range) text colour = blue.

    example:
    john 1
    sam 2
    james 1


    and to keep: Private Sub Worksheet_Change(ByVal Target As Range) for automatic updates.


    This would be a great starting block for me - many thanks!
    Last edited by pretzelz; 12-10-2011 at 09:01 PM.

  9. #9
    Registered User
    Join Date
    11-20-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: advanced conditional formatting based on text value list

    color progress.xls -- file attached

  10. #10
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: advanced conditional formatting based on text value list

    Name the cells with pupil's names "Pupils" and the cells with the pupil's progress "Progress".
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    11-20-2011
    Location
    london, england
    MS-Off Ver
    Excel 2003
    Posts
    5

    Question text colour based on another cell's value

    colour progress tracker.xls

    Foxguy, your help is invaluable. However, I have put the code in, added a missing bracket, but it is having no effect on the data in the worksheet. Would you possibly be able to take a look at the attached file and see where I've gone wrong? I'll happily send you $10 for the working solution.

    Many thanks.

  12. #12
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: advanced conditional formatting based on text value list

    You put it in the wrong module.

    Right mouse click the worksheet tab that you want it to work on and select "View Code". Put the code in the window that appears.

    Event subs (like Worksheet_Change(), Worksheet_SelectionChange(), Worksheet_Activate(), etc) only work on one worksheet - the worksheet they are attached to. So they have to be in the module that is assigned to that worksheet.

    You could create a Workbook_SheetChange() sub that works on every worksheet. It has to be located in the "ThisWorkbook" module.

    Macros that are put in a Standard Module are available to all worksheets, but Event Subs don't work in those modules.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1