Freshman,

You can loop through the shapes looking for the text: this version will only show the fill of the
shape with the matching text,

Private Sub Worksheet_Change(ByVal target As Range)
Dim sh As Shape

If target.Address < "$A$1" Then Exit Sub

For Each sh In ActiveSheet.Shapes
If sh.TextFrame.Characters.Text = target.Value Then
With sh
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.SchemeColor = 17
End With
Else
sh.Fill.Visible = msoFalse
End If
Next sh

End Sub



HTH,
Bernie
MS Excel MVP


"Freshman" wrote in message
...
Dear experts,

Jacob Skaria, MVP wrote me a marco below for the solution of conditional
formatting on autoshapes. As Jacob's macro refers the value of A1 to the
default name of the autoshapes, such as: the value of A1 = 2, then the
autoshape named "Oval 2" will be changed to colour green. However, I want the
value of A1 refers to the text inside Oval 2 instead, such as: value A1 =
table and the text inside Oval 2 = table, then autoshpae Oval 2 will turn
into colour green. How can I get it done? Please kindly advise.

Thanks in advance.

QUOTE

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Shape, intCount As Integer
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
For intCount = 1 To 10
On Error Resume Next
Set sh = ActiveSheet.Shapes("Oval " & intCount)
If Not sh Is Nothing Then
With sh
.Fill.Visible = msoTrue
.Fill.Solid
.Fill.ForeColor.SchemeColor = IIf(intCount = Target, 17, 1)
End With
ActiveSheet.DrawingObjects("Oval " & intCount).Text = "Hi" & intCount
ActiveSheet.DrawingObjects("Oval " & intCount).Font.ColorIndex = _
IIf(intCount = Target, 2, xlAutomatic)
ActiveSheet.DrawingObjects("Oval " & intCount).Font.Bold = (intCount =
Target)
End If
Set sh = Nothing
Next
End If
End Sub

UNQUOTE