vba 在if语句（函数）中使用单元格的颜色作为条件

Use cell's color as condition in if statement (function)

提问by 1337Atreyu

I am trying to get a cell to perform a function based on the hilight color of a cell.

我试图让一个单元格根据单元格的高亮颜色执行一个功能。

Here is the function I currently have:

这是我目前拥有的功能：

=IF(A6.Interior.ColorIndex=6,IF(ROUNDDOWN(IF(M6<3,0,IF(M6<5,1,IF(M6<10,3,(M6/5)+2))),0)=0,0,ROUNDDOWN(IF(M6<3,0,IF(M6<5,1,IF(M6<10,2,(M6/5)+2))),0)),IF(ROUNDDOWN(IF(M6<7,0,IF(M6<10,1,M6/5)),0)=0,0,ROUNDDOWN(IF(M6<7,0,IF(M6<10,1,M6/5)),0)))

Just so you don't have to read through all of that, here's a more simple example

为了让您不必通读所有内容，这里有一个更简单的示例

=IF(A6.Interior.ColorIndex=6,"True","False")

All that his is returning is #NAME? . Is there any way that I can do this as a function in a cell or is VBA absolutely required?

他回来的只是#NAME？. 有什么方法可以将其作为单元格中的函数来执行，还是绝对需要 VBA？

Thanks,

谢谢，

Jordan

约旦

采纳答案by Andy G

You cannot use VBA (Interior.ColorIndex) in a formula which is why you receive the error.

您不能Interior.ColorIndex在公式中使用 VBA ( )，这就是您收到错误的原因。

It is not possible to do this without VBA.

没有 VBA 就不可能做到这一点。

Function YellowIt(rng As Range) As Boolean
    If rng.Interior.ColorIndex = 6 Then
        YellowIt = True
    Else
        YellowIt = False
    End If
End Function

However, I do not recommend this: it is not how user-defined VBA functions (UDFs) are intended to be used. They should reflect the behaviour of Excel functions, which cannot read the colour-formatting of a cell. (This function may not work in a future version of Excel.)

但是，我不建议这样做：这不是用户定义的 VBA 函数 (UDF) 的使用方式。它们应该反映 Excel 函数的行为，它无法读取单元格的颜色格式。（此功能可能不适用于未来版本的 Excel。）

It is far better that you base a formula on the original condition(decision) that makes the cell yellow in the first place. Or, alternatively, run a Sub procedure to fill in the True or False values (although, of course, these values will no longer be linked to the original cell's formatting).

最好将公式基于使单元格首先变黄的原始条件（决定）。或者，运行一个 Sub 过程来填充 True 或 False 值（当然，这些值将不再链接到原始单元格的格式）。

回答by Joe

I don't believe there's any way to get a cell's color from a formula. The closest you can get is the CELLformula, but (at least as of Excel 2003), it doesn't return the cell's color.

我不相信有任何方法可以从公式中获取单元格的颜色。您可以获得的最接近的是CELL公式，但是（至少从 Excel 2003 开始​​），它不会返回单元格的颜色。

It would be pretty easy to implement with VBA:

用 VBA 实现会很容易：

Public Function myColor(r As Range) As Integer
    myColor = r.Interior.ColorIndex
End Function

Then in the worksheet:

然后在工作表中：

=mycolor(A1)

回答by Nick M

Although this does not directly address your question, you can actually sort your data by cell colour in Excel (which then makes it pretty easy to label all records with a particular colour in the same way and, hence, condition upon this label).

虽然这不能直接解决您的问题，但您实际上可以在 Excel 中按单元格颜色对数据进行排序（这样可以很容易地以相同的方式用特定颜色标记所有记录，因此，以此标签为条件）。

In Excel 2010, you can do this by going to Data -> Sort -> Sort On "Cell Colour".

在 Excel 2010 中，您可以通过转到“数据”->“排序”->“按“单元格颜色”排序来执行此操作。

回答by Greg Barth

I had a similar problem where I needed to only show a value from another Excel cell if the font was black. I created this function: `Option Explicit

我有一个类似的问题，如果字体为黑色，我只需要显示另一个 Excel 单元格中的值。我创建了这个函数：`Option Explicit

Function blackFont(r As Range) As Boolean If r.Font.Color = 0 Then blackFont = True Else blackFont = False End If

函数 blackFont(r As Range) As Boolean If r.Font.Color = 0 Then blackFont = True Else blackFont = False End If

End Function `

结束函数`

In my cell I have this formula: =IF(blackFont(Y51),Y51," ")

在我的单元格中，我有这个公式： =IF(blackFont(Y51),Y51," ")

This worked well for me to test for a black font and only show the value in the Y51 cell if it had a black font.

这对我来说很有效，可以测试黑色字体，并且只在 Y51 单元格中显示黑色字体时的值。

回答by emirjonb

The only easy solution that I have applied is to recreate the primary condition that do the highlights as an IFcondition and use it on the IFformula. Something like this. Depending on the highlight condition the formula will change but I think that should be recreated (es. highlight greater than 20).

我应用的唯一简单解决方案是重新创建将高亮显示为IF条件的主要条件，并将其用于IF公式。像这样的东西。根据高亮条件，公式会改变，但我认为应该重新创建（例如，高亮大于 20）。

=IF(B3>20,(B3)," ")