Highlighting Row

[straw]

Dear all,

I am a newbie in VBA and encounter a problem. Please help me.

My excel sheet has several columns and hundred rows. Each column represents different items (eg.: Invoice number, transaction date, completion etc). Now I would like to create a VBA which will search the "Completion" column (Column C) for the word of "Completed". When it is "completed", then that row (from Column A to Column I) will be filled with color in order to easily search uncompleted transaction.

Since I am too superficial, what I can do is just highlight the "completed" cell only :<

Belows are what I wrote


Sub Highlight()

Dim clnC As Range
Dim clcell As Range

Set clnC = Range("C9:C10000")

For Each clcell In clnC

If clcell.Value = "Completed" Then
clcell.Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

End If

Next clcell

End Sub

Thanks in advance for your help.

[JKwan]

try this out:
[VBA]
Sub Highlight()
Dim LastRow As Long
Dim InputSheet As Worksheet
Dim lRow As Long

Set InputSheet = Worksheets("Sheet1")

LastRow = FindLastRow(InputSheet, "C")
For lRow = 9 To LastRow
If UCase(InputSheet.Cells(lRow, "C")) = "COMPLETED" Then
InputSheet.Range("A" & lRow & ":I" & lRow).Interior.ColorIndex = 6
End If
Next lRow

Set InputSheet = Nothing
End Sub
Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String) As Long
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = WS.Range(ColumnLetter & "65536").End(xlUp).Row
End Function
[/VBA]

[straw]

Dear JKawn,

Thank you very much. I really appreciate your help. But first thing...let me digest your work first : )

Thanks again!