Excel VBA to determine greatest column populated by group

[AppStaff]

Can anyone suggest a function or procedure to return a cell location based on these conditions in this type of worksheet?

Worksheet is

Project Status Date Date2 Date3...
CR0664 Complete 1 1
CR0664 Complete 1
CR0884 Cancelled 1

I'd like to determine the last cell by a given project that is populated. In this example I'd want to know the cell reference for CR0664 and Date 3 and the cell reference for CR0884 and Date

Ultimately what I want to do is color the last cell for a project in the date it was completed or cancelled (black for complete and red for cancelled). I think this would be easier for me if there weren't multiple lines for many projects.

I was thinking first filter on Completed projects then filter on each project. Define the last row and column. Then loop through each column by row looking for a value until it finds one. When it does mark it.

I'm having trouble implementing this however. if I could get a function that just gives me the cell reference by project then I'm pretty sure I can do the rest. (excel vba newbie). Any help is appreciated on this

 

[SkipVought]

Hi,

I think this would be easier for me if there weren't multiple lines for many projects.

Actually, it WOULD be easier. However with a normalized table structure something like this...
[tt]
Project Status Date

[/tt]
where the Date field would contain a REAL DATE.

Then it's just a matter of finding the maximum date for each Project -- I'd use a PivotTable and get the answer in 5 seconds.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[AppStaff]

Actually, believe it or not this is based on normalized tables. Its the product of a cross-tab query (like a pivot table I believe though I'm not very versed with pivot tables).

The tables are a historical picture of project data. The cross tab shows each period of data capture what status the project was and if it was present at all.

The end product is a gantt like view where the cells are colored based on the status to show the progress and changes by month.

I neglected to provide a full sample because I have the first part working. But the table looks more like this which will show you why there is duplication.

Project status closed? 1/20/05 1/30/05 etc...
CR0084 In progress Completed 1
CR0084 Pending Completed 1
CR0085 In progress 1 1


Note: sorry having trouble getting columns straight but the 1's are the values under the date columns.
I use this code to color the bars based on status. The part I'm struggling with is automating the marking of where the project ends. Any ideas? Perhaps you've led me to some alternative I can explore. I'll look into providing another table that lists the last month and then just color the cell based on that table.

Sub ColorCell()
Dim i, e, c As Integer

For i = 1 To 5000

Select Case Cells(i, 4).Value

Case "In Progress" 'green
c = 10
Case "On-Hold" 'yellow
c = 6
Case "Pending" 'Yellow
c = 6
Case "Committed" 'blue
c = 5
Case "Targeted" 'blue
c = 5
End Select

    For e = 7 To 22
    If Cells(i, e).Value > 0 Then
    Cells(i, e).Interior.ColorIndex = c
    Else
    End If
    
    Next e
Next i
End Sub