Solved: format cells based on 2 criteria

[john3j]

I would like to write a subroutine that runs on a particular worksheet called "Invoice Summary". From here, I would like it to find the last row in the worksheet and count up through Column H. The start of the range to be counted starts at H7 and of course ends at whatever the last row is. I would like the code to go through each row and if there is a number greater that $0.00 left in column G then color the cells in column G and H based on the date in Column H.

If the year of the date in column H is equal to 2007 then the color should be light green.

If the year of the date in column H is equal to 2008 then the color should be light yellow.

If the year of the date in column H is equal to 2009 then the color should be light pink.

If the year of the date in column H is equal to 2010 then the color should be light orange.

If anyone could help make quick work of this it would be great!

Thanks

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
For i = 7 To LastRow

Select Case Year(Cells(i, "H").Value)

Case 2007: .Interior.ColorIndex = 35 'light green
Case 2008: .Interior.ColorIndex = 36 'light yellow
Case 2009: .Interior.ColorIndex = 38 'light pink
Case 2010: .Interior.ColorIndex = 45 'light orange
End If
Next i
End With

End Sub

[john3j]

hey the code didnt do anything and i was getting a compile error or something. changed the end if statement to an end select statement but it still didnt color the expiring fields. Please keep in mind that it is supposed to color cells in G and H depending on what the year is in column H. I have included a sample. We could create a button to do this if needed. Please let me know what you think.

[Bob Phillips]

Try this version



Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim iCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
For i = 7 To LastRow

With Cells(i, "H")

Select Case Year(.Value)

Case 2007: iCol = 35 'light green
Case 2008: iCol = 36 'light yellow
Case 2009: iCol = 38 'light pink
Case 2010: iCol = 45 'light orange
End Select

.Offset(0, -1).Resize(, 2).Interior.ColorIndex = iCol
End With
Next i
End With

End Sub

[john3j]

works great! thanks a lot for the help!