| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Text Color
I am getting an error with the Me object, I have virtually no VBA experience
and as such I have no idea what other object to use here... Private Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 1 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Thanks, Todd |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and Text Color
Try
Private Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Activesheet.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 1 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Me refers to a worksheet, workbook, form or some other class, and has to be used within that code module.. -- __________________________________ HTH Bob "FP Novice" wrote in message ... I am getting an error with the Me object, I have virtually no VBA experience and as such I have no idea what other object to use here... Private Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 1 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Thanks, Todd |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and Text Color
The "Me" object can be used only in Object Modules (Class modules,
ThisWorkbook, UserForms, and the Sheet modules). You cannot use it in regular code modules. Me always refers to the object in which it appears. So, for example, if you use it in Sheet1, it refers to Sheet1. Use it in ThisWorkbook, it refers to ThisWorkbook. If you need to specify a worksheet for your range, use something like Set rng = Worksheets("Sheet2").Range("G6") Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 19 Feb 2009 09:55:03 -0800, FP Novice wrote: I am getting an error with the Me object, I have virtually no VBA experience and as such I have no idea what other object to use here... Private Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 1 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Thanks, Todd |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and Text Color
Thank you, that worked and made complete sense. Both your and Chip's
explanations are great. I have another question: Why does protecting a cell override the macro? That is, once the cells are protected and locked the macro no longer runs. "Bob Phillips" wrote: Try Private Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Activesheet.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 1 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Me refers to a worksheet, workbook, form or some other class, and has to be used within that code module.. -- __________________________________ HTH Bob "FP Novice" wrote in message ... I am getting an error with the Me object, I have virtually no VBA experience and as such I have no idea what other object to use here... Private Sub Color_G6() Dim Num As Long Dim rng As Range Set rng = Me.Range("G6") On Error GoTo endit Application.EnableEvents = False 'Determine the color Select Case UCase(rng.Value) Case Is = "BLUE": Num = 5 'blue Case Is = "ORANGE": Num = 45 'orange Case Is = "GREEN": Num = 10 'green Case Is = "BROWN": Num = 53 'brown Case Is = "SLATE": Num = 15 'slate Case Is = "WHITE": Num = 1 'black Case Is = "RED": Num = 3 'red Case Is = "BLACK": Num = 1 'black Case Is = "YELLOW": Num = 6 'yellow Case Is = "VIOLET": Num = 54 'violet Case Is = "ROSE": Num = 38 'rose Case Is = "AQUA": Num = 42 'aqua End Select 'Apply the color rng.Font.ColorIndex = Num endit: Application.EnableEvents = True End Sub Thanks, Todd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I copy color text & paste keeping color | Excel Discussion (Misc queries) | |||
| Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
| Make text color match cell color with macro? | Excel Discussion (Misc queries) | |||
| Cond. format text color based on another's text color | Excel Discussion (Misc queries) | |||
| Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) | |||
Linear Mode
