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

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

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

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

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

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