choosing from drop down list

[Guest]

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??

 

[Bob Phillips]

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)

 

[Guest]

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

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)

 

[Dan R.]

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

 

[Guest]

That's got it. thanks Dan

 

[Dan R.]

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