Complex conditional formatting

Hi,

I know of no way with standard excel functionality to detect a cell colour change. You could use a UDF

e.g.

Function cell5(r As Range)
    Application.Volatile
    If r(1, 5).Interior.ColorIndex = 6 Then
        cell5 = WorksheetFunction.Sum(r)
    Else
        cell5 = WorksheetFunction.Sum(r.Resize(1, 4))
    End If

End Function

In the 6th cell in the row enter

Formula:

=cell5(A1:E1)

The only downside to this is that the sum won't immediately change when you colour the 5th cell yellow. You will need to do a recalc. i.e. by hitting the F9 key, edit and entering a cell, clicking on a row or column dividing line or one of the several other ways to calculate the sheet.