| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code Question
While searching the discussion group for a solution to overcome the
conditional formatting limit of 3, I found an entry by Gord Dibben. It is a source code entry that I was hoping would change the color of the text to the given value when my VLOOKUP returned certain results. Specifically I am entering a number and VLOOKUP returns one of twelve colors, I want the color of the text to match the word that was retrieved. It does not work as entered in VB as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("H6")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput '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.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code Question
hi
what is it not doing? Regards FSt1 "FP Novice" wrote: While searching the discussion group for a solution to overcome the conditional formatting limit of 3, I found an entry by Gord Dibben. It is a source code entry that I was hoping would change the color of the text to the given value when my VLOOKUP returned certain results. Specifically I am entering a number and VLOOKUP returns one of twelve colors, I want the color of the text to match the word that was retrieved. It does not work as entered in VB as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("H6")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput '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.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code Question
It does not change the color of the text, the color of the text remains the
same after every lookup. "FSt1" wrote: hi what is it not doing? Regards FSt1 "FP Novice" wrote: While searching the discussion group for a solution to overcome the conditional formatting limit of 3, I found an entry by Gord Dibben. It is a source code entry that I was hoping would change the color of the text to the given value when my VLOOKUP returned certain results. Specifically I am entering a number and VLOOKUP returns one of twelve colors, I want the color of the text to match the word that was retrieved. It does not work as entered in VB as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("H6")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput '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.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code Question
I think I know why it does not work, I do not know how to make it work... In
cell H6 (where the results are supplied) the cell contents remain a VLOOKUP formula even though I 'see' "BLUE" "ORANGE" etc... the cell is still a formula thus VBA does not see a qualifier and therefore will not change the color of the text... Does this make sense? If so how do I make it work? "FP Novice" wrote: It does not change the color of the text, the color of the text remains the same after every lookup. "FSt1" wrote: hi what is it not doing? Regards FSt1 "FP Novice" wrote: While searching the discussion group for a solution to overcome the conditional formatting limit of 3, I found an entry by Gord Dibben. It is a source code entry that I was hoping would change the color of the text to the given value when my VLOOKUP returned certain results. Specifically I am entering a number and VLOOKUP returns one of twelve colors, I want the color of the text to match the word that was retrieved. It does not work as entered in VB as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("H6")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput '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.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code Question
Try this revision. Note the Calculate event.
Private Sub Worksheet_Calculate() Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Me.Range("H6") 'or H1:H20 If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Offset(0, 1).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.Offset(0, 1).Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Gord On Fri, 16 May 2008 12:14:02 -0700, FP Novice wrote: I think I know why it does not work, I do not know how to make it work... In cell H6 (where the results are supplied) the cell contents remain a VLOOKUP formula even though I 'see' "BLUE" "ORANGE" etc... the cell is still a formula thus VBA does not see a qualifier and therefore will not change the color of the text... Does this make sense? If so how do I make it work? "FP Novice" wrote: It does not change the color of the text, the color of the text remains the same after every lookup. "FSt1" wrote: hi what is it not doing? Regards FSt1 "FP Novice" wrote: While searching the discussion group for a solution to overcome the conditional formatting limit of 3, I found an entry by Gord Dibben. It is a source code entry that I was hoping would change the color of the text to the given value when my VLOOKUP returned certain results. Specifically I am entering a number and VLOOKUP returns one of twelve colors, I want the color of the text to match the word that was retrieved. It does not work as entered in VB as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("H6")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput '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.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code Question
Please ignore this.
I mis-read and screwed up per usual. Get back to you later. Gord On Fri, 16 May 2008 16:43:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Try this revision. Note the Calculate event. Private Sub Worksheet_Calculate() Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Me.Range("H6") 'or H1:H20 If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Offset(0, 1).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.Offset(0, 1).Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Gord On Fri, 16 May 2008 12:14:02 -0700, FP Novice wrote: I think I know why it does not work, I do not know how to make it work... In cell H6 (where the results are supplied) the cell contents remain a VLOOKUP formula even though I 'see' "BLUE" "ORANGE" etc... the cell is still a formula thus VBA does not see a qualifier and therefore will not change the color of the text... Does this make sense? If so how do I make it work? "FP Novice" wrote: It does not change the color of the text, the color of the text remains the same after every lookup. "FSt1" wrote: hi what is it not doing? Regards FSt1 "FP Novice" wrote: While searching the discussion group for a solution to overcome the conditional formatting limit of 3, I found an entry by Gord Dibben. It is a source code entry that I was hoping would change the color of the text to the given value when my VLOOKUP returned certain results. Specifically I am entering a number and VLOOKUP returns one of twelve colors, I want the color of the text to match the word that was retrieved. It does not work as entered in VB as follows: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("H6")) If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput '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.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vb code question | Excel Discussion (Misc queries) | |||
| vb code question | Excel Discussion (Misc queries) | |||
| vb code question | Excel Discussion (Misc queries) | |||
| Another code question | Excel Discussion (Misc queries) | |||
| VBA code question | Excel Discussion (Misc queries) | |||
Hybrid Mode
