Lessons Learned (after examining the workbook and code that Pivot Man sent to
me)
#1 - VB is case sensitive! while most worksheet functions are not, VB is,
and it had some impact in testing results: "$c$3" is not the same as "$C$3"
in VB.
#2 - We must keep in mind that the Worksheet_Change() event is not triggered
by the recalculation of a worksheet formula! Pivot Man was testing for
changes in cells with formulas - we didn't know that, and hadn't asked. The
code now tests for changes made to cells that the formula is dependent upon,
which do get changes from the keyboard.
#3 - The devil is in the details like that, and sometimes we just don't ask
the right questions.

Here is the final code that actually does function:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Single ' need single for decimal
Dim upperLimit As Single ' need single for decimal
Dim lowerColor As Long
Dim upperColor As Long
Dim defaultColor As Long
Dim testValue As Single

Application.EnableEvents = False
Select Case Target.Address
Case "$A$3", "$B$3"
testValue = Range("C3").Value
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 0.8
upperLimit = 0.95
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case "$A$4", "$B$4"
testValue = Range("C4").Value
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 0.85
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case "$A$5", "$B$5"
testValue = Range("C5").Value
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 3")
lowerLimit = 0.7
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case "$A$6", "$B$6"
testValue = Range("C6").Value
Set anyShape = Worksheets("Sheet1").Shapes("Oval 4")
lowerLimit = 0.75
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$A$7", "$B$7"
testValue = Range("C7").Value
Set anyShape = Worksheets("Sheet1").Shapes("Oval 5")
lowerLimit = 0.75
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents = True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If testValue < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf testValue upperLimit Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape = Nothing
End Sub

"Pivot Man" wrote:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks