My code works for a single row, but how do I make it work for all rows?

[tentsu]

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

[patel]

Sub status()
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To LR
If Sheet1.Range("O" & r).Value = "" Then
If Sheet1.Range("L" & r).Value <= Date Then
Sheet1.Range("B" & r).Value = "Late"
Else
Sheet1.Range("B" & r).Value = "Open"
End If
End If
If Sheet1.Range("O" & r).Value <> "" Then
If Sheet1.Range("L" & r).Value <= Date Then
Sheet1.Range("B" & r).Value = "Closed"
End If
End If
'change color
If Sheet1.Range("B" & r).Value = "Closed" Then
Sheet1.Range("A" & r & ":O" & r).Interior.Color = RGB(0, 255, 0)
End If
If Sheet1.Range("B" & r).Value = "Late" Then
Sheet1.Range("A" & r & ":O" & r).Interior.Color = RGB(255, 0, 0)
End If
Next
End Sub

[SamT]

tentsu,

Welcome to VBAExpress. If you click the # button, you can paste your code in between the inserted

**Paste here** square bracketed CODE tags and your VBA code will be auto-formatted. You can also select the VBA code after pasting, then click the button.

Conditional formatting is the better way to color the cells than VBA, unless; you also have some code that checks the cell color to perform some action.

IMO, if your table is smaller than about 100 rows, you would be better advised to use a formula in column "B" than VBA.
IF($O2="",IF($L2<NOW(),"OPEN","LATE"),IF($L2<NOW(),"CLOSED","OPEN")) Paste that formula in B2 and copy it down.

This code will run on every Cell in that you select. IMPORTANT: Only selects cells in Column B.
Sub status()

''''Select the used cells in Column "B" before running

Dim Cel As Range

With ActiveSheet
For Each Cel In Selection
If .Range("O2").Value = "" Then
If .Range("L2").Value <= Date Then
Cel.Value = "Late"
Else
Cel.Value = "Open"
End If
Else
If .Range("L2").Value <= Date Then _
Cel.Value = "Closed"
End If
Next Cel
End With

End Sub

Since it runs thru the entire Selection every time, you can choose a column to test for changes and automatically run it only on the row that contains the cell that changed in that column. For more information on this, Type "WorkSheet" in the VB Editor (VBE) keep the cursor in, or next to, the word and press F1.

Browse the upcoming Help file to the Worksheet Object and study the actions under the Events link.