1. ## Monster Loop (2003)

Hi,

I am trying to create what could be a monster loop. I have a worksheet 'Master Output' that I would like to script the following? Providing there is data (value) in column I (worksheet runs A-L with any number of rows) >>> If J starts with 11, turn row font colour red. If J starts 80, turn row font colour blue. Any cells in F,G or H equal to blank or zero, add text 'No Data Found'.

I am having a go at the minute but am not getting there very fast. I think I may be attempting the impossible? I'm not sure that this is achievable in one??

Any pointers appreciated!

Thanks
Nath

2. ## Re: Monster Loop (2003)

First pointer would be to attach a spreadsheet with before and expected results. It reduces the assumption factor.

Does this do what you are looking for?

Sub woody()
Dim i As Long, lRow As Long, jStr As String, iColor As Integer
lRow = Cells(60000, 10).End(xlUp).Row

For i = 2 To lRow

If Cells(i, 6) = "" Or Cells(i, 6) = 0 Then
Cells(i, 6) = "No Data Found"
End If

If Cells(i, 7) = "" Or Cells(i, 7) = 0 Then
Cells(i, 7) = "No Data Found"
End If

If Cells(i, 8) = "" Or Cells(i, 8) = 0 Then
Cells(i, 8) = "No Data Found"
End If

jStr = Left(Cells(i, 10), 2)
Select Case jStr

Case "11"
iColor = 3
Case "80"
iColor = 5
Case Else
iColor = Cells(i, 10).Font.ColorIndex
End Select

Cells(i, 10).Font.ColorIndex = iColor

Next

End Sub

3. ## Re: Monster Loop (2003)

It does, yes thanks. Only, I need the whole rows to change font colour based on 11 and 80 in J. Is that possible?

4. ## Re: Monster Loop (2003)

Use:
Rows(i).Font.ColorIndex = iColor

Cells(i,10).Font.ColorIndex = iColor

Steve

5. ## Re: Monster Loop (2003)

Thanks Steve. That did it.

6. ## Re: Monster Loop (2003)

Sub Format2()

Dim i As Long, lRow As Long, jStr2 As String, iColor As Integer
lRow = Cells(60000, 9).End(xlUp).Row

Sheets("Rejections 2").Select

For i = 2 To lRow
jStr2 = Cells(i, 1)
Select Case jStr2

Case "PURGED ITEMS"
iColor = 7
Case Else
iColor = Cells(i, 11).Font.ColorIndex
End Select

Rows(i).Font.ColorIndex = iColor

Next

End Sub

This turns the rows with "Purged Items" to font pink. What I want it to do is turn the cells within A-K to fill yellow. I changed font to fill but that did'nt work, but also I dont want to affect the entire rows.

Thanks
Nath

7. ## Re: Monster Loop (2003)

Use:
iColor = <font color=red>6</font color=red> 'Yellow

iColor = Cells(i, 11).<font color=red>Interior</font color=red>.ColorIndex

<font color=red>Range(Cells(i, 1), Cells(i, 11)).Interior</font color=red>.ColorIndex = iColor

Steve

8. ## Re: Monster Loop (2003)

Thanks Steve!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•