Having Sheet1 containing number of people per Resource and Week,
Sheet2 Range E2:I10 contains number of hours per Resource and week
based on interiorcolor and forecolor of each Cell in Sheet1 Range
E2:I10
InteriorColor = None = 8 hours per day RGB 255,255,255
InteriorColor = Light Green = 9 hours per day RGB 153,255,153
InteriorColor = Light Yellow = 10 hours per day RGB 255,255,102
ForeColor = Black = 5 days a week ColorIndex 1
ForeColor = Blue = 6 days a week ColorIndex 5
ForeColor = Red = 7 days a week ColorIndex 3
Sheet3 Range E2:I10 contains Cost per Resource and Week
Based on Hours in Sheet2 Range E2:I10 * Average Rate in Sheet1 Range
B2:B10
Sheet1 Range C2:C10 contains Total Hours per Resource from Sheet2
Range E2: I10
Sheet1 Range D2:D10 contains Total Cost per Resource from Sheet3 Range
E2 : I10
Sheet1 header is as follows :
Resource, AvgRate, Hours, Cost, Week1, Wk2, Wk3, Wk4, Wk5
Aside from my syntax needing correction, how will I address my
variable
iCol for Cell Interiorcolor if it is not a ColorIndex value but an RGB
value ?
---- Pseudo Code --------------
Sub Cost()
Dim ws1,ws2, ws3 as Worksheets
Dim rng as Range
Dim iCol as ? ' InteriorColor
Dim fCol as Integer ' forecolor
Dim AvgRate as Integer
Set rng as Range(E2:I10)
set ws1 as ThisWorksheet

--------------------------------------------------------------
For Each Cell in rng
AvgRate = ActiveCell(Row,"B")
iCol = ActiveCell.Interiorcolor : fCol = ActiveCell.Forecolor
Select Case iCol
Case is …. ' Light Green
Select Case fColor
Case 5 ' Blue
ws2.ActiveCell.value = ws1.ActiveCell.Value * 9 *
6
ws3.ActiveCell.value = ws1.ActiveCell.Value * 9 *
6 * AvgRate
Case 3 ' Red
ws2.ActiveCell.value = ws1.ActiveCell.Value * 9 *
7
ws3.ActiveCell.value = ws1.ActiveCell.Value * 9 *
7 * AvgRate
Case Else ' Black
ws2.ActiveCell.value = ws1.ActiveCell.Value * 9
* 5
ws3.ActiveCell.value = ws1.ActiveCell.Value * 9
* 5 * AvgRate
End Select
Case is …. ' Light Yellow
Select Case fColor
....
Case Else
....
End Select
Next Cell
'Sum Hours from ws2 to ws1 Column "C"
Set rng as Range(C2:C10)
For Each Cell in rng
Sum ws2(Row:"E:I")
Next Cell
'Sum Cost from ws3 to ws1 Column "D"
Set rng as Range(D2:D10)
For Each Cell in rng
Sum ws3(Row:"E:I")
Next Cell
End Sub

Thank you for your help

Celeste