Monster Loop (2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

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

Steve

Reply | Quote

Thanks Steve. That did it.

Reply | Quote

Using your code, I have also added:

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.

Help please?

Thanks
Nath

Reply | Quote

Use:
iColor = 6 ‘Yellow

iColor = Cells(i, 11).Interior.ColorIndex

Range(Cells(i, 1), Cells(i, 11)).Interior.ColorIndex = iColor

Steve

Reply | Quote

Thanks Steve!

Reply | Quote