"Conditional Formatting" with symbols.

G

GoKats78

Board Regular
Joined
Aug 22, 2008
Messages
166
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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:
Code: 
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.
 
Upvote 0
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...
 
Upvote 0
I think I figured it out...

Code: 
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
 
Upvote 0
Well done! It looks like you've worked that out fine. :-)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA Replace All '/' in a sheet, except for formulas
Replies
4
Views
163
MARK858
MARK858
O
  • Question Question
Frustrating intermittent VBA error dialog telling me that "User-define Type Not Defined"
Replies
1
Views
257
Micron
M
O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
129
OaklandJim
O
B
  • Question Question
selecting a date from a list of pre-defined dates
Replies
2
Views
267
Blanchetdb
B
J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
84
RoryA
RoryA

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top