"Conditional Formatting" with symbols.

[GoKats78]

Excel 03....
I need some code to conditionally format cells based on a dropdown which consists of symbols.

This is the dropdown menu.
■ Required
◙ Legally Required
▲ Recommended
▼ Optional
Ø Not Required

I need the symbols to be color coded..based on the selection.
I have code that works for text...but not symbols.

 

[GlennUK]

Do you mean that you have an in-cell drop-down, for Data Validation, and you'd like the format of the chosen item to be the same as you've shown?

If so, you could have some sheet code, similar to this to do it:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsError(Application.Match(Target.Value, [sourcelist], 0)) Then
        Target.Font.ColorIndex = xlAutomatic
        Target.Characters(1, 1).Font.ColorIndex = Range("SourceList").Cells(Application.Match(Target.Value, [sourcelist], 0)).Characters(1, 1).Font.ColorIndex
    End If
End Sub

... I don't have access to Excel 2003 at this particular moment, so can't test it on that, but it works fine in Excel 2007, and I've not used anything specific to that version.

 

[GoKats78]

Perfect!
Thanks!ray:ray:

 

[GlennUK]

That's great!

 

[GoKats78]

I will be able to use this in many places!

 

[GoKats78]

How would I code it if I wanted to limit the range that is affected by this code/

For example...the range that only needs formatted is F7:AD19...

 

[GoKats78]

I think I figured it out...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("f7:Ad19")) Is Nothing Then
    If Not IsError(Application.Match(Target.Value, [c1:c5], 0)) Then
 
        Target.Font.ColorIndex = xlAutomatic
        Target.Characters(1, 1).Font.ColorIndex = Range("c1:c5").Cells(Application.Match(Target.Value, [c1:c5], 0)).Characters(1, 1).Font.ColorIndex
    End If
    End If
End Sub

 

[GlennUK]

Well done! It looks like you've worked that out fine.