Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing from drop down list
This code will change the font on the entire row red when "PRA" appears in
column H: Sub stantial() Dim myRange As Range Set myRange = Range("H8:H400") For Each c In myRange c.Select If c.Value = "PRA" Then Selection.EntireRow.Select Selection.Font.ColorIndex = 3 End If Next End Sub It works when you type in the letters, but when picked from a drop down list, it doesn't. Why would that be?? -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing from drop down list
Sub stantial()
Dim myRange As Range, c As Range Set myRange = Range("H8:H400") For Each c In myRange If c.Value = "PRA" Then c.EntireRow.Font.ColorIndex = 3 End If Next End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jock" wrote in message ... This code will change the font on the entire row red when "PRA" appears in column H: Sub stantial() Dim myRange As Range Set myRange = Range("H8:H400") For Each c In myRange c.Select If c.Value = "PRA" Then Selection.EntireRow.Select Selection.Font.ColorIndex = 3 End If Next End Sub It works when you type in the letters, but when picked from a drop down list, it doesn't. Why would that be?? -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing from drop down list
Thanks Bob, but I can't get it to work. Trying to type the text in also drew
a blank which contradicts what I wrote earlier! I tried using "Private Sub Worksheet_Change ()" instead which would change the text to red as required, but didn't change it back to default when "PRA" was changed/removed. Is there another way around this problem? Help! -- Traa Dy Liooar Jock "Bob Phillips" wrote: Sub stantial() Dim myRange As Range, c As Range Set myRange = Range("H8:H400") For Each c In myRange If c.Value = "PRA" Then c.EntireRow.Font.ColorIndex = 3 End If Next End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jock" wrote in message ... This code will change the font on the entire row red when "PRA" appears in column H: Sub stantial() Dim myRange As Range Set myRange = Range("H8:H400") For Each c In myRange c.Select If c.Value = "PRA" Then Selection.EntireRow.Select Selection.Font.ColorIndex = 3 End If Next End Sub It works when you type in the letters, but when picked from a drop down list, it doesn't. Why would that be?? -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing from drop down list
Jock,
Try something like this: Private Sub Worksheet_Change(ByVal myRange As Range) Dim c As Range Set myRange = Range("H8:H400") For Each c In myRange If c.Value = "PRA" Then c.EntireRow.Font.ColorIndex = 3 Else c.EntireRow.Font.ColorIndex = 0 End If Next End Sub -- Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing from drop down list
That's got it. thanks Dan
-- Traa Dy Liooar Jock "Dan R." wrote: Jock, Try something like this: Private Sub Worksheet_Change(ByVal myRange As Range) Dim c As Range Set myRange = Range("H8:H400") For Each c In myRange If c.Value = "PRA" Then c.EntireRow.Font.ColorIndex = 3 Else c.EntireRow.Font.ColorIndex = 0 End If Next End Sub -- Dan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
choosing from drop down list
That's some sloppy code I posted. Here's a better way to do it:
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRange As Range Set myRange = Range("H8:H400") If Target.Count 1 Then Exit Sub If Not Intersect(Target, myRange) Is Nothing Then If Target.Value = "PRA" Then Target.EntireRow.Font.ColorIndex = 3 Else Target.EntireRow.Font.ColorIndex = 0 End If End If End Sub -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Choosing multiple items in a drop down list | Excel Discussion (Misc queries) | |||
Populate Multiple cells when choosing one selection from drop down | Excel Discussion (Misc queries) | |||
excel 2003 drop down time choosing | Excel Discussion (Misc queries) | |||
Auto Fill Cells, When Choosing From Drop-Down List... | Excel Worksheet Functions | |||
choosing from a list in VB | Excel Programming |