advanced conditional formatting based on text value list

[pretzelz]

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!

[6StringJazzer]

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

[foxguy]

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.

[6StringJazzer]

Can't be done in 2003 with more than 3 colors.

True, but it sounds like the request is just for two colors....

[foxguy]

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.

[pretzelz]

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!

...

[6StringJazzer]

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.

[pretzelz]

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!

[pretzelz]

color progress.xls -- file attached

[foxguy]

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.

[pretzelz]

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.

[foxguy]

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.