tentsu
09-04-2013, 06:25 AM
Hello. I'm creating a program that indicates the status of a project as either "Open", "Late", or "Closed", and assigns a color to represent each status (with "Open" being represented by white). I have figured out how to do this for just a single row in the spreadsheet, but I would like for it to be applied to all rows. The issue is that my code only relates to a specific cell when considering the status; how can I adjust this so that it considers each row individually and applies the status? I am working in Excel 2003. My code is below, where cell O2 = completed date, cell L2 = expected completion date, and cell B2 = status. The range ("A2:O2") corresponds to the "2" row. Any insight would be greatly appreciated. Thank you.
(the periods at each line are there to show indents and make the If statements easier to follow)
Sub status()
' set status
If Sheet1.Range("O2").Value = "" Then
If Sheet1.Range("L2").Value <= Date Then
Sheet1.Range("B2").Value = "Late"
Else
Sheet1.Range("B2").Value = "Open"
End If
End If
If Sheet1.Range("O2").Value <> "" Then
If Sheet1.Range("L2").Value <= Date Then
Sheet1.Range("B2").Value = "Closed"
End If
End If
'change color
If Sheet1.Range("B2").Value = "Closed" Then
Sheet1.Range("A2:O2").Interior.Color = RGB(0, 255, 0)
End If
If Sheet1.Range("B2").Value = "Late" Then
Sheet1.Range("A2:O2").Interior.Color = RGB(255, 0, 0)
End If
End Sub
(the periods at each line are there to show indents and make the If statements easier to follow)
Sub status()
' set status
If Sheet1.Range("O2").Value = "" Then
If Sheet1.Range("L2").Value <= Date Then
Sheet1.Range("B2").Value = "Late"
Else
Sheet1.Range("B2").Value = "Open"
End If
End If
If Sheet1.Range("O2").Value <> "" Then
If Sheet1.Range("L2").Value <= Date Then
Sheet1.Range("B2").Value = "Closed"
End If
End If
'change color
If Sheet1.Range("B2").Value = "Closed" Then
Sheet1.Range("A2:O2").Interior.Color = RGB(0, 255, 0)
End If
If Sheet1.Range("B2").Value = "Late" Then
Sheet1.Range("A2:O2").Interior.Color = RGB(255, 0, 0)
End If
End Sub