用户定义的改变单元格颜色的函数

I've seen many users asking questions trying to change the colors of cells using User-defined functions. I was always under the impression that it was not possible to do so. My understanding was that a user-defined function cannot change any properties of a cell except the value of the cell that contains the formula. Subs are what change cells themselves.

我曾看到许多用户提出问题，试图使用用户定义的函数改变单元格的颜色。我一直觉得这样做是不可能的。我的理解是，用户定义的函数不能改变单元格的任何属性，除了包含公式的单元格的值。Subs是改变细胞本身的东西。

However, when playing around with some code to test this, I found that it's not always the case.
Using the simple code:

然而，当使用一些代码进行测试时，我发现情况并非总是如此。使用简单的代码:

Function ColorCell(rng As Range)
If rng.Value = 1 Then
   ColorCell = False
Else
   ColorCell = True
   rng.Interior.ColorIndex = 3
End If
End Function

If I enter the function into a cell, I achieve expected results, no cells change colors. However, if I use the Formulas > Insert Function button and navigate to my formula to insert it this way, it does color the targeted cells.

如果我将函数输入到一个单元格中，就会得到预期的结果，没有单元格会改变颜色。但是，如果我使用公式>插入函数按钮并导航到我的公式中以这种方式插入它，它会给目标单元格着色。

How is this possible, and why did the function behave differently when entered in different ways?

这是怎么可能的，为什么函数在输入时表现不同?

EDIT: this was tested using Excel 2007

编辑:这是使用Excel 2007测试的

4 个解决方案

#1

use this code...just replace sheet name and try

使用这个代码…只需替换表名并尝试

Sheets("sheet_name").range(j:j).clear

for j=2 to 15
if Sheets("sheet_name").Cells(j, 1).value=1 then

else

Sheets("sheet_name").Cells(j, 1).Interior.ColorIndex = 3
next j

#2

I use Worksheet_Change event to detect value change in working range. Example.I want to do something when range A1:A5 has been change. I use below event.

我使用Worksheet_Change事件来检测工作范围内的值变化。的例子。当范围A1:A5发生变化时，我想做点什么。我使用下面的事件。

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(<Your working range>)) Is Nothing Then 'Define range that you want to do
       'Statement here
       ...

    End If
End Sub

When the range value has been change. It will execute your code.

And other way. Use Conditional Formatting.

当量程值变化时。它将执行您的代码。和其他方法。使用条件格式。

#3

As we all find out sooner or later, in user functions you can't access subs that change things in your spreadsheet directly.

正如我们迟早会发现的那样，在用户函数中，您不能直接访问更改电子表格中的内容的子代。

But try this:

但试试这个:

Dim ColorMeTarget As Range, ColorMeVal As Long

Public Function ColorMe(ByVal TargetRange As Range, ByVal ColVal As Long)
  Set ColorMeTarget = TargetRange
  ColorMeVal = ColVal
  ColorMe = ColVal
End Function

Public Sub ColorMeSub()
  Application.OnTime Now + TimeValue("00:00:05"), "ColorMeSub"
  If ColorMeTarget.Interior.Color <> ColorMeVal Then ColorMeTarget.Interior.Color = ColorMeVal
End Sub

If you run the sub first, it will constantly scan the static variables ColorMeTarget and ColorMeVal to see if there is a change. The function ColorMe will set these values. Some additional code is needed in case ColorMeTarget is not yet initialized.

如果你先运行子变量，它会不断地扫描静态变量ColorMeTarget和ColorMeVal，看看是否有变化。函数ColorMe将设置这些值。如果ColorMeTarget尚未初始化，则需要一些附加代码。

If you get smarter, you could have the function first check to see if there is indeed a change and add the new coloring requests to a stack. Your reoccurring sub can then 'catch up', especially if you have many functions like this.

如果您变得更聪明，您可以首先检查函数，看看是否确实有更改，然后将新的着色请求添加到堆栈中。你重新出现的子程序就可以“赶上”，特别是当你有很多这样的函数时。

You can then even have all kinds of additional controls added to your function/macro--EVEN STUFF NOT COVERED BY THE LATEST VERSIONS OF 'CONDITIONAL FORMATING'!!! YAY!!!!

然后，你甚至可以在你的函数/宏中添加各种各样的附加控件——甚至是最新版本的“条件格式”所没有涵盖的东西!耶! ! ! !

Something to try: In some of my automated macros, I am able to set OnTime through a function but cannot make it work here. It would be cleaner to have the function set the OnTime and not have a reoccuring sub that needs initializing.

值得尝试的事情:在我的一些自动宏中，我能够通过一个函数设置OnTime，但不能使它在这里工作。让函数设置为OnTime，而不设置需要初始化的重新出现子节点，这样会更简洁。

#4

could also try the non-script method of auto-coloring a cell based on the condition of the cell (aka Conditional Formatting):

也可以尝试使用非脚本的方法，根据单元格的条件自动着色单元格(也称为条件格式):