Polish my code, please
 

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

Polish my code, please
 

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

Polish my code, please
 

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=507521