Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to produce color font in data validation selections
Here is code I use to create data validation by selecting text and then when
text is selected a code replaces the text and all works fine. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Application.EnableEvents = True End If End Sub Sub MyFix() Application.EnableEvents = True End Sub What kind of code do I need so that my selections would appear in different font rather than the default black. I know you can't change font size or list size but color would be nice to help distinguish between selections. Below is 3 such descriptions I would like to have show up in differnet colors. Employee Training, Seminars, Management and Professional Education Courses. Employee Welfare, department lunch, food provided for in-house training, Manager-Employee Lunch Postage expense Telephone (Not in Hotel) Any help is appreciated. -- Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to produce color font in data validation selections
Not supported in the Validation box itself. You can expand you code to
color the interior of the cell if you wish. -- Regards, Tom Ogilvy "bdehning" wrote in message ... Here is code I use to create data validation by selecting text and then when text is selected a code replaces the text and all works fine. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Application.EnableEvents = True End If End Sub Sub MyFix() Application.EnableEvents = True End Sub What kind of code do I need so that my selections would appear in different font rather than the default black. I know you can't change font size or list size but color would be nice to help distinguish between selections. Below is 3 such descriptions I would like to have show up in differnet colors. Employee Training, Seminars, Management and Professional Education Courses. Employee Welfare, department lunch, food provided for in-house training, Manager-Employee Lunch Postage expense Telephone (Not in Hotel) Any help is appreciated. -- Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to produce color font in data validation selections
hi,
the line of code you want is: Range("??").Font.ColorIndex = 3 'red to find out all of the colorindexes, in a blank workbook copy, paste and run the following code. Sub macGetColors() ' Macro written 2/10/02 by FSt1 Sheets("Sheet1").Select Range("B2").Select Set ci = Range("A1") ci.Value = 1 Set c = Range("B2") Do Until ci 56 Set c2 = c.Offset(1, 0) Set cnum = c.Offset(0, 1) c.Interior.ColorIndex = ci.Value c.Offset(0, 1) = ci.Value ci.Value = ci.Value + 1 Set c = c2 c.Select Loop end sub Regards Frank -----Original Message----- Here is code I use to create data validation by selecting text and then when text is selected a code replaces the text and all works fine. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range ("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Application.EnableEvents = True End If End Sub Sub MyFix() Application.EnableEvents = True End Sub What kind of code do I need so that my selections would appear in different font rather than the default black. I know you can't change font size or list size but color would be nice to help distinguish between selections. Below is 3 such descriptions I would like to have show up in differnet colors. Employee Training, Seminars, Management and Professional Education Courses. Employee Welfare, department lunch, food provided for in-house training, Manager-Employee Lunch Postage expense Telephone (Not in Hotel) Any help is appreciated. -- Brian . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to produce color font in data validation selections
Here is what I tried with no luck. My list is under Codelist.(D5-D28) What
am I doing wrong? I thought I would try one line to start. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Range("D7").Font.ColorIndex = 3 Application.EnableEvents = True End If End Sub "Frank Stone" wrote: hi, the line of code you want is: Range("??").Font.ColorIndex = 3 'red to find out all of the colorindexes, in a blank workbook copy, paste and run the following code. Sub macGetColors() ' Macro written 2/10/02 by FSt1 Sheets("Sheet1").Select Range("B2").Select Set ci = Range("A1") ci.Value = 1 Set c = Range("B2") Do Until ci 56 Set c2 = c.Offset(1, 0) Set cnum = c.Offset(0, 1) c.Interior.ColorIndex = ci.Value c.Offset(0, 1) = ci.Value ci.Value = ci.Value + 1 Set c = c2 c.Select Loop end sub Regards Frank -----Original Message----- Here is code I use to create data validation by selecting text and then when text is selected a code replaces the text and all works fine. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range ("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Application.EnableEvents = True End If End Sub Sub MyFix() Application.EnableEvents = True End Sub What kind of code do I need so that my selections would appear in different font rather than the default black. I know you can't change font size or list size but color would be nice to help distinguish between selections. Below is 3 such descriptions I would like to have show up in differnet colors. Employee Training, Seminars, Management and Professional Education Courses. Employee Welfare, department lunch, food provided for in-house training, Manager-Employee Lunch Postage expense Telephone (Not in Hotel) Any help is appreciated. -- Brian . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to produce color font in data validation selections
What is it you want to do?
It sounded like you wanted to color the entries in the dropdown (which isn't possible). Do you want to color the cell that contains the dropdown based on what selection is made? -- Regards, Tom Ogilvy "bdehning" wrote in message ... Here is what I tried with no luck. My list is under Codelist.(D5-D28) What am I doing wrong? I thought I would try one line to start. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Range("D7").Font.ColorIndex = 3 Application.EnableEvents = True End If End Sub "Frank Stone" wrote: hi, the line of code you want is: Range("??").Font.ColorIndex = 3 'red to find out all of the colorindexes, in a blank workbook copy, paste and run the following code. Sub macGetColors() ' Macro written 2/10/02 by FSt1 Sheets("Sheet1").Select Range("B2").Select Set ci = Range("A1") ci.Value = 1 Set c = Range("B2") Do Until ci 56 Set c2 = c.Offset(1, 0) Set cnum = c.Offset(0, 1) c.Interior.ColorIndex = ci.Value c.Offset(0, 1) = ci.Value ci.Value = ci.Value + 1 Set c = c2 c.Select Loop end sub Regards Frank -----Original Message----- Here is code I use to create data validation by selecting text and then when text is selected a code replaces the text and all works fine. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 12 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Eig Expense Coding").Range ("B4") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Eig Expense Coding").Range("CodeList"), 0), 0) Application.EnableEvents = True End If End Sub Sub MyFix() Application.EnableEvents = True End Sub What kind of code do I need so that my selections would appear in different font rather than the default black. I know you can't change font size or list size but color would be nice to help distinguish between selections. Below is 3 such descriptions I would like to have show up in differnet colors. Employee Training, Seminars, Management and Professional Education Courses. Employee Welfare, department lunch, food provided for in-house training, Manager-Employee Lunch Postage expense Telephone (Not in Hotel) Any help is appreciated. -- Brian . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple selections for data validation | Excel Discussion (Misc queries) | |||
Amend the DV0022 - Update Validation Selections code for more lists | Excel Worksheet Functions | |||
create a formula to produce a color in excel | Excel Worksheet Functions | |||
How can the font in the Data Validation list be changed? | Excel Worksheet Functions | |||
Sum a row of data based on FONT COLOR ( | Excel Programming |