excel下拉更改颜色_自动更改Excel形状颜色-CSDN博客

excel下拉更改颜色

We all want to be remembered for our contributions to humanity. That's why, way back in 2004, I made an Excel Smiley Face sample (aka "Happy Face"). There was a new version in 2009, and here's another update, to change the Smiley Face shape colour automatically. You can do the same thing with other shapes too, like partial circle "pie slice". Is there a Nobel Prize in this category?

我们所有人都希望为我们对人类的贡献而被铭记。这就是为什么在2004年，我制作了一个Excel Smiley Face样本(又名“ Happy Face”)的原因。 2009年有一个新版本，这是另一个更新，可以自动更改“笑脸”形状的颜色。您也可以对其他形状执行相同的操作，例如部分圆形“饼图切片”。此类别是否有诺贝尔奖？

原始的Excel笑脸 (Original Excel Smiley Face)

I don't remember what inspired my first Excel Smiley Face sample file. Maybe someone asked a question in the old Excel newsgroups, and that was my answer.

我不记得是什么启发了我的第一个Excel Smiley Face示例文件。也许有人在旧的Excel新闻组中提出了一个问题，那就是我的答案。

Anyway, the worksheet has a Smiley Face shape, and a cell where you can enter a whole number between 1 and 100.

无论如何，工作表都具有一个“笑脸”形状和一个单元格，您可以在其中输入1到100之间的整数。

单元格数据验证 (Number Cell Data Validation)

In cell H3, where the number is entered, there is a data validation rule that controls what can be typed in the cell.

在输入数字的单元格H3中，有一条数据验证规则控制着可以在单元格中键入的内容。

As you can see in the screen shot below, you're only allowed to enter a Whole number, and it must be between the Minimum of zero and the Maximum of 100.

从下面的屏幕快照中可以看到，只允许输入一个整数，并且该整数必须介于0的最小值和100的最大值之间。

笑脸嘴巴曲线 (Smiley Face Mouth Curve)

If you click on the Smiley Face shape to select it, you'll see handles on the shape's border.

如果单击“笑脸”形状以将其选中，则会在形状的边框上看到手柄。

There's also a yellow handle on the mouth, and you can drag that up or down, to change the mouth's curve.

嘴上还有一个黄色手柄，您可以向上或向下拖动以更改嘴的曲线。

录制宏 (Record a Macro)

If you record a macro while you select the shape and then drag that mouth handle, you'll get code similar to this screen shot.

如果在选择形状时记录宏，然后拖动该嘴部手柄，则将获得类似于此屏幕截图的代码。

自动更改曲线 (Change the Curve Automatically)

In the Happy Face sample file, there is code similar to that, and it runs automatically when you type a number in cell H3.

在Happy Face示例文件中，有与此类似的代码，当您在单元格H3中键入数字时，它将自动运行。

There are 2 versions of the Happy Face code in the sample file, one for Excel 2003, and one for Excel 2007 and later.

示例文件中有2个版本的Happy Face代码，一个版本用于Excel 2003，一个版本用于Excel 2007及更高版本。

I had to create that new code because the angle settings change in Excel 2007, but I'm not sure why. You can read more about that change to the code in the Smiley Face Chart Update article.

我必须创建该新代码，因为角度设置在Excel 2007中会更改，但是我不确定为什么。您可以在“笑脸图表更新”文章中阅读有关代码更改的更多信息。

参见嘴曲线代码 (See the Mouth Curve Code)

To see the Excel 2007 code, right-click Happy Face Gauge 2007 sheet tab, and click View Code.

若要查看Excel 2007代码，请右键单击“ Happy Face Gauge 2007”工作表选项卡，然后单击“查看代码”。

There is a Worksheet_Change procedure, and it changes the mouth's curve if the worksheet change was made in cell H3.

有一个Worksheet_Change过程，如果在单元格H3中进行了工作表更改，它将更改嘴巴的曲线。

If Target.Address = "$H$3" Then
    Application.EnableEvents = False
    sh.Adjustments.Item(1) _
      = myMin + (myMax - myMin) _
      * Target.Value / 100
End If

更改脸部颜色 (Change the Face Colour)

Recently, someone asked if it was possible to change the face colour too, along with the mouth curve.

最近，有人问是否也可以改变脸部颜色以及嘴巴曲线。

Well, that sounded like fun, so I've made a new version of the Excel Smiley Face, and it also changes colour, based on the number in H3.

好吧，这听起来很有趣，所以我制作了新版本的Excel Smiley Face，它还根据H3中的数字更改了颜色。

There are numbers on the worksheet, to explain when the colour changes, but that list is for reference only.

工作表上有数字，以解释颜色何时更改，但该列表仅供参考。

修改后的工作表_更改代码 (Revised Worksheet_Change Code)

To make the colour change, I added a new section in the Worksheet_Change code.

为了更改颜色，我在Worksheet_Change代码中添加了一个新部分。

There's a new variable, myColor, and it's based on the number in H3. The 90 and 60 are typed in that code, so you could change them there, if you want to set different limits.

有一个新变量myColor，它基于H3中的数字。 90和60是在该代码中键入的，因此，如果要设置不同的限制，则可以在其中进行更改。

'change shape colour
'less than 60% = red
'60%- 90% Amber
'90%-100% Green
  Select Case Target.Value
    Case Is >= 90: myColor _
      = RGB(146, 208, 80)  'green
    Case Is >= 60: myColor _
      = RGB(255, 192, 0) 'orange
    Case Else: myColor _
      = RGB(255, 0, 0) 'red
  End Select

  sh.Fill.ForeColor.RGB = myColor

So, if the number in H3 is less than 60, the face changes to red.

因此，如果H3中的数字小于60，则脸部变为红色。

形状RGB设置 (Shape RGB Setting)

To set the colour for the shape, I had to get the RGB settings for each colour:

要设置形状的颜色，我必须获得每种颜色的RGB设置：

Green: RGB(146, 208, 80)
绿色：RGB(146、208、80)
Orange: RGB(255, 192, 0)
橙色：RGB(255，192，0)
Red: RGB(255, 0, 0)
红色：RGB(255，0，0)

If you'd like to use other colours, here's how you can find their RGB settings:

如果您想使用其他颜色，请按照以下方法找到它们的RGB设置：

Right-click a cell, and click the arrow beside the Fill Color button
右键单击一个单元格，然后单击“填充颜色”按钮旁边的箭头
Click More Colors
单击更多颜色
Click a color on the Standard tab, then click the Custom tab
单击标准选项卡上的颜色，然后单击自定义选项卡
For Color Model, choose RGB
对于颜色模型，选择RGB
Make note of the Red, Green and Blue numbers
记下红色，绿色和蓝色数字
Click Cancel, to close the window
单击取消，关闭窗口

Then, edit the Worksheet_Change code, to use your new RGB settings.

然后，编辑Worksheet_Change代码，以使用新的RGB设置。

其他类型的形状 (Other Types of Shapes)

You could use the same technique to change the fill colour for different types of shapes – maybe your co-workers aren't the "Happy Face" type!

您可以使用相同的技术更改不同类型的形状的填充颜色-也许您的同事不是“幸福的面Kong”类型！

There's another sheet in the new Happy Face file, and it has a "Partial Circle" shape. Change the percentage in cell H3, and the "pie slice" changes size, and the colour changes too.

新的Happy Face文件中还有另一个工作表，它的形状为“偏圆”。更改单元格H3中的百分比，“饼图切片”将更改大小，颜色也会更改。

There are 4 colours for this shape, and those are shown on the worksheet.

该形状有4种颜色，这些颜色显示在工作表上。