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

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"

Sub SetColors()
    Dim rCell as Range
    Dim lRow as Long

    For Each rCell in Range("LevelRows")
        lRow = rCell.Value
        Range(Pupils).Rows(rCell.Row).Interior.ColorIndex = Range(LevelColors).Row(lRow).Interior.Colorindex
    Next rCell
End Sub

Originally Posted by 6StringJazzer

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.

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!

Name the cells with pupil's names "Pupils" and the cells with the pupil's progress "Progress".

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rPupil as Range
    Dim lColor as XLColorIndex

    With ActiveSheet
        If Not Intersect(Target, .Range("Progress") Is Nothing Then  
            Set rPupil = .Range("Pupils").EntireColumn.Cells(Target.Row,1)
            With Target 
               If .Value < 0 Then
                  lColor = 3    'Red
               ElseIf .Value = 1 Then
                  lColor = 35   'Green
               ElseIf .Value = 2 Then
                  lColor = 5    'Blue
               ElseIf .Value = 3 Then
                  lColor = 38   'Pink 
               ElseIf .Value = 4 Then
                  lColor = 39   'Purple
               Else
                  lColor = XlNone
               End If
            End With
            rPupil.Font.ColorIndex = lColor
        End If
    End With                              

End Sub

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.