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

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" <(E-Mail Removed)> wrote in message
news:A8666CAB-96AD-4D2D-B326-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:A8666CAB-96AD-4D2D-B326-(E-Mail Removed)...
> > 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
>
>
>

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

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

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