Conditional Format Stop Light? Can I do This

[rockyw]

I have data in a shape from a formula. I tried to change the text color based on a goal but conditional format is grayed out. Is there a way to make a shape change color with conditional formating? If I could change the text or the shape would be great. Thanks for any help.

 

[rockyw]

I tried this but I get an error.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("Sheet2!B14")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value < 5 Then
ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = vbGreen
End If
End If
End If
End Sub

My cell with data is in Sheet2 B14, shape is Oval5 Thanks

 

[rockyw]

All I woiuld like to do is get the shape to be red or green based on the data in Sheet 2 F 14 Thanks

 

[Snakehips]

rockyw,

Your code gets half the job done ok.

Try..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Sheet2!F14")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value < 5 Then
ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = vbGreen
Else
ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub

NB you refer to B14 in your code then F14 in post #3 ???
Hope that helps.

 

[rockyw]

I tried this but I get no change, the Oval 5 is on a sheet called 2014, the cell I'm looking at is on Sheet2. Is that what is wrong?

 

[Snakehips]

I tried this but I get no change, the Oval 5 is on a sheet called 2014, the cell I'm looking at is on Sheet2. Is that what is wrong?

Yes, being in different sheets requires a code change.

This in the Sheet2 code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F14")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value < 5 Then
Sheets("2014").Shapes("Oval 5").Fill.ForeColor.RGB = vbGreen
Else
Sheets("2014").Shapes("Oval 5").Fill.ForeColor.RGB = vbRed
End If
End If
End If
End Sub