1。 右键单击要更改形状颜色的工作表选项卡，然后选择 查看代码 从上下文菜单中，弹出 Microsoft Visual Basic for Applications 窗口，请将以下代码复制并粘贴到空白处 模块 窗口。
Private Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice 20160704 If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If IsNumeric(Target.Value) Then If Target.Value < 100 Then ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbRed ElseIf Target.Value >= 100 And Target.Value < 200 Then ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbYellow Else ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = vbGreen End If End If End Sub
注意：在上面的代码中， A1 是你的形状颜色将根据和改变的单元格值 椭圆形1 是插入形状的形状名称，您可以将其更改为您的需要。
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 10 months agoHi... excellent solution... but how do I apply it to multiple shapes based on the corresponding values of a range of cells. Many thanks in advance for your help.
To post as a guest, your comment is unpublished.· 11 months agoHow do I make the private sub to read the result from the AVERAGE(C1,C5,C9) calculation?
Sub only works with numeric values; any thoughts and suggestions are greatly appreciated.
To post as a guest, your comment is unpublished.· 1 years agoThanks for this which is really useful.
I now want to use it with a pivot table on another worksheet which controls the data on the sheet with the shapes that I want to change colour. However, when I change the selection on the pivot table the data on the worksheet with the shapes is updated but the code does not run so the shapes do not change colour
If I manually change the values the code runs and the colour of the shapes is updated.
Question: what do i need to add to the code above to allow it to run automatically?
To post as a guest, your comment is unpublished.· 1 years agoHow can this be applied if you have multiple shape in the same worksheet?
To post as a guest, your comment is unpublished.· 2 years agoGreat vba solution.
It is possible to also use conditional formatting to colour the shapes.
Set the name of each shape as the cell value. Using a With Each Shape then set the shape colour as the cell colour for all named shapes.
The cell colour may be changed using conditional formatting based on numerical values.
For example the colour of a semi transparent overlap on a city map can be used to graphically indicate population density per block with a graduated colour scheme.
To post as a guest, your comment is unpublished.· 2 years agoI have 300 shapes in a sheet. Is it possible to check the adjacent or linked cell's value (empty or non-empty) in a sheet and color the linked shapes through VBA code?
To post as a guest, your comment is unpublished.· 2 years agoHow about if we have more than 1 object in the worksheet which the colors change according to the value input say in A1, B1,C1....