Create formula that will pull a value based on text in diff cell?
 

In Excel 2000 - I need to create a formula that will pull a value, from on a
cell other than the one that the value is being pulled, based on text or
color. Conditional formatting will color a cell based on a condition but I
need to be able to define a condition based on either a color or text. For
example, text/color is in cell a1, a3, & a5 the value is in a2, a4, & a6. I
need to create a formula in cell a7 that will add the values from a2, a4, &
a6 based on the condition (color or text) in cell a1, a3, & a5.