vba 基于背景颜色的 Excel 公式单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41998676/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Formula Cell Based on Background color
提问by Etienne
I need a formula in EXCEL that place a number 1 in the cell next to the cell where the cell background is RED. See example below.
我需要一个 EXCEL 公式，在单元格背景为红色的单元格旁边的单元格中放置一个数字 1。请参阅下面的示例。
Is this possible at all without VBA?
如果没有 VBA，这可能吗？
回答by Glitch_Doctor
This can be done from Name Managerthis can be accessed by pressing Ctrl+F3.
这可以Name Manager通过按Ctrl+访问F3。
You will want to create a named reference (i called this "color") and have it refer to =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))in the formula bar.
您将要创建一个命名引用（我称之为“颜色”）并=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))在公式栏中引用它。
Now you can use this 1 cell to the right to determine the color index number of a cell:
现在您可以使用右侧的 1 个单元格来确定单元格的颜色索引号：
So as red is color index 3 in the cell next to it you can apply the formula:
因此，由于红色是旁边单元格中的颜色索引 3，您可以应用以下公式：
=IF(color=3,1,0)
=IF(color=3,1,0)
回答by Michael
You can achieve it manually without VBA using an autofilter:
您可以使用自动过滤器在没有 VBA 的情况下手动实现它：
Make sure you have a title above the column with colours and above the column where you want the value 1 placed
Add an Autofilter (Select both columns, click the Filter button on the Data tab of the ribbon)
Click the drop down filter on the column with colours, then click on Filter by Colour, the choose the Red colour
In your second column, enter a 1 in every visible cell. (Enter 1 in the first cell, then fill down. Or, select all cells, type 1 then press ctrl-enter)
确保在带有颜色的列上方和要放置值 1 的列上方有标题
添加自动过滤器（选择两列，单击功能区数据选项卡上的过滤器按钮）
单击带有颜色的列上的下拉过滤器，然后单击按颜色过滤，选择红色
在第二列中，在每个可见单元格中输入 1。（在第一个单元格中输入 1，然后向下填充。或者，选择所有单元格，输入 1 然后按 ctrl-enter）
回答by CallumDA
Open the VBA editor and add a new module. Do this by going to the Developertab and clicking Visual Basic. If you don't have the developer tab on the ribbon you will need to add it (do a quick Google search). Once the VBA editor is open, right click on the VBA project which has your workbook name on the left and insert a module.
打开 VBA 编辑器并添加一个新模块。通过转到Developer选项卡并单击来执行此操作Visual Basic。如果功能区上没有开发人员选项卡，则需要添加它（在 Google 上进行快速搜索）。打开 VBA 编辑器后，右键单击左侧有您的工作簿名称的 VBA 项目并插入一个模块。
Place the following code into the new module:
将以下代码放入新模块中：
Function IsRed(rng As Range) As Integer
IsRed = (rng.Interior.Color = vbRed) * -1
End Function
then you can use the formula =IsRed(A1)to determine if A1has a red background
那么你可以使用公式=IsRed(A1)来确定是否A1有红色背景
note: this uses the default red in the standard colours
注意：这使用标准颜色中的默认红色
