Polish my code, please

[ddd]

Hi - trying to hammer out a macro (see below). If any values in Column C contain any one of the following values: "Consent Order" or "Review" or varying characters containing the word "Closed"....thought a wildcard around Closed would take care of that, then I want the font color for all the values in that particular row(s) to turn red.

Below is what I've hammered out (don't laugh )......it's not working so sure would appreciate some help. THANK YOU.

Dim myrange
myrange = ActiveSheet.Columns("c").Value
If myrange = ["review", "consent order", "*closed*"] Then
ActiveSheet.Rows.Select
Selection.Font.Color = vbRed
End If
End Sub

[JMB]

I think this will do what you want:

Sub Test2()
Dim MyRange As Range
Dim x As Range
Set MyRange = Application.Intersect(ActiveSheet.UsedRange, _
Columns("C"))

For Each x In MyRange
If x.Value = "review" Or _
x.Value = "consent order" Or _
x.Value Like "*closed*" Then _
Rows(x.Row).Font.Color = vbRed
Next x
End Sub

"ddd" wrote:

>
> Hi - trying to hammer out a macro (see below). If any values in Column C
> contain any one of the following values: "Consent Order" or "Review" or
> varying characters containing the word "Closed"....thought a wildcard
> around Closed would take care of that, then I want the font color for
> all the values in that particular row(s) to turn red.
>
> Below is what I've hammered out (don't laugh )......it's not
> working so sure would appreciate some help. THANK YOU.
>
> Dim myrange
> myrange = ActiveSheet.Columns("c").Value
> If myrange = ["review", "consent order", "*closed*"] Then
> ActiveSheet.Rows.Select
> Selection.Font.Color = vbRed
> End If
> End Sub
>
>
> --
> ddd
> ------------------------------------------------------------------------
> ddd's Profile: http://www.excelforum.com/member.php...o&userid=31068
> View this thread: http://www.excelforum.com/showthread...hreadid=507521
>
>

[duane]

I'm sure there are better ways to do it, but this seemed to work.

Note I limited it to rows 1:100 in column C

Sub macro1()
Dim rng As Range
For i = 1 To 100
Set rng = Cells(i, 3).Find("review")
If rng Is Nothing Then GoTo trynext
myrow = rng.Row
GoTo setcolor
trynext:
Set rng = Cells(i, 3).Find("consent order")
If rng Is Nothing Then GoTo trynext1
myrow = rng.Row
GoTo setcolor
trynext1:
Set rng = Cells(i, 3).Find("closed")
If rng Is Nothing Then GoTo nextrow
myrow = rng.Row
setcolor:
Rows(myrow).Select
Selection.Font.Color = vbRed
nextrow:
Next i
End Sub