Loop through column, colour row based on criteria

[Silver Fox]

Dear all,

I'm looking for a macro that will loop through column I and colour the row from A to I one of three colours, depending on the text content of the cell. Can anyone help? I'm really making an effort to learn VBA on the job, but a deadline is approaching, so any help would be much appreciated.

Many thanks.

[Silver Fox]

Dear all,

I have managed to crawl to a solution. Just in case it is of use to anyone, the code is:



Sub ColourRows ()

Dim IRow As Integer
Dim IColour As Integer
Dim v As String

IRow = 1
Do
v = Range("I" & IRow).Value
Select Case v
Case "Distribution list": IColour = 36
Case "English, Post (International Address)": IColour = 35
Case "English, Post (Russian Address)": IColour = 37
Case "": IColour = xlNone
End Select
Range("A" & IRow, "I" & IRow).Select
Selection.Interior.ColorIndex = IColour
IRow = IRow + 1
Loop Until v = "" And Range("I" & IRow + 1) = ""

End Sub

[Charlize]

This is the one that I had in mind.

Sub test()
Dim rowno As Long
Dim loopno As Long
Dim norows As Long
norows = Sheets(1).Cells(Rows.Count, "I").End(xlUp).row
rowno = 1
For loopno = 1 To norows
Select Case Range("I" & rowno).Value 'I = 1 or 2 or 3
Case 1
Worksheets(1).Range("A" & rowno & ":I" & rowno).Interior.ColorIndex = 3
Case 2
Worksheets(1).Range("A" & rowno & ":I" & rowno).Interior.ColorIndex = 6
Case 3
Worksheets(1).Range("A" & rowno & ":I" & rowno).Interior.ColorIndex = 9
Case Else
Worksheets(1).Range("A" & rowno & ":I" & rowno).Interior.ColorIndex = 12
End Select
rowno = rowno + 1
Next loopno
End Sub
Charlize

- or put the values in an array and for each item in the array colour your row.