Summing values based on matching text in cells

[ce13]

I'm new to this forum but I'm hoping someone can help me out, I've looked for an answer and can't seem to find one anywhere.

I'm creating a list of names of faculty members and the amount of time they will be working on a project. This list will be added to from a bunch of people in the office. They will add names of faculty members and the amount if time they are expecting them to work in a given semester in separate columns. What I'm trying to do is add together all the hours that each faculty member is expect to work in a semester and make sure it falls below a certain amount of hours. If it's more then that then excel will apply conditional formatting and turn the persons name red and bold. I think I could do this using SUMIFS but the problem is that I don't know what the names will be added ahead of time and therefore I don't know how to enter the compare criteria. Does anyone have a suggestion on how to accomplish my goal?

Thanks for your responses ahead of time.

 

[JLGWhiz]

The first thing that needs to be done for any project in VBA is to define the problem, because in defining the problem, the solution is obvious.
In this case, assumptions will work and you can modify those as required.
Assuming that faculty member names in columns means row one of the column as headers, then we can assume they begin in column 2. That leaves us to assume that a fixed number of tasks would performed on the project would be listed in column A, with hours for each task entered appropriately on the same row under the faculty member name who participated in that task. We can further assume that these hours would be totaled on a row at the bottom of each faculty menember column. We will also assume that the totals row is the last row with data on that sheet. So it does not matter who adds names, so long as they do not duplicate a previously added name. Since the hous limit was not specified, we will assume it is 40 hours per person, which seems reasonable over a semester where an hour at a time might be spent during a regular workday by a single individual during their break or at lunch time or whenever they are not doing what they get paid to do. So with these assumtions in hand:

Code:
Sub CheckHours()
Dim sh As Worksheet, lr As Long, lc As Long, rng As Range
Set sh = Sheets(1) 'Edit sheet name
'Get last row number
lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
'Get last column number
lc = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
Set rng = sh.Range(sh.Cells(lr, 2), sh.Cells(lr, lc)) 'define the totals row and range
With sh
For i = 2 To lc 'Use For...Next loop to check totals
If .Cells(lr, i) >= 40 Then 'Limit criteria
With .Cells(1, i)
.Font.Bold = True
.Interior.ColorIndex = 3
End With
End If
Next
End With
End Sub
Code:

Of course, if any of the assumtions are wrong, the code fails.