The autoshapes always have text. You could try this - change

If sh.TextFrame.Characters.Text = target.Value Then

to

If sh.TextFrame.Characters.Text = CStr(target.Value) Then

You could also format your target cell as string.

HTH,
Bernie
MS Excel MVP


"Freshman" wrote in message
...
Hi Bernie,

Thanks for your tips. What about if I input numbers into the autoshapes
instead of text. How you change the macro? Please kindly advise.

Thanks a million.

"Bernie Deitrick" wrote:

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


.