vba 根据另一个单元格的颜色设置一个单元格的颜色

Set the color of one cell based on the color of another cell

提问by yue86231

What I would like to have is:

我想要的是：

IF   A1 in Sheet 2 is blue  
Then A1 in Sheet 1 changes to blue

I know I can get the color of A1 in Sheet 2 by using:

我知道我可以使用以下方法获得 Sheet 2 中 A1 的颜色：

=GET.CELL(63,Sheet2!A1)

(Excel: Can I create a Conditional Formula based on the Color of a Cell?)

（Excel：我可以根据单元格的颜色创建条件公式吗？）

But I can't figure out what I should do in the next step.

但是我不知道下一步应该做什么。

Any suggestions?

有什么建议？

#

Update on 12.01.2015 sorry if I gave too little information about my case, and not pointed out at the beginning clearly whether I want to do it in VBA or not. At the beginning I thought a function would work, but as I considered my file, a function might not work at all.

2015 年 1 月 12 日更新，抱歉，如果我提供的关于我的案例的信息太少，并且在开始时没有明确指出我是否想在 VBA 中执行此操作。一开始我认为一个函数会起作用，但是当我考虑我的文件时，一个函数可能根本不起作用。

It is about the output of a correlation analyse from SPSS, there are three columns: correlation coefficient, p-value and sample size. I need to check the coefficient and p-value at the same time, and present the coefficient in a readable way. Say I run a correlation between 50 variables with 100 variables, I would not paste coefficient and p-value in one sheet, rather:

它是关于 SPSS 相关分析的输出，有三列：相关系数、p 值和样本量。我需要同时检查系数和 p 值，并以可读的方式呈现系数。假设我在 50 个变量与 100 个变量之间运行相关性，我不会将系数和 p 值粘贴在一张纸中，而是：

sheet one : coefficient sheet two: p-value

表一：系数表二：p 值

What I would to have is:

我想要的是：

If value of p-value is bigger than 0.05, then coefficient (cell) changes to blue/dark blue or black.

如果 p 值大于 0.05，则系数（单元格）变为蓝色/深蓝色或黑色。

So that when I watch the first sheet, I know blue ones should be ignored because of non-significance.

所以当我看第一张纸时，我知道蓝色的应该被忽略，因为不重要。

I will try all the suggestions below and report it later.

我会尝试下面的所有建议并稍后报告。

回答by sancho.s ReinstateMonicaCellio

What you need is a way to detect changes in cell format. There appears to be no event that triggers upon change in format. See How to detect changes in cell format?

您需要的是一种检测单元格格式变化的方法。似乎没有在格式更改时触发的事件。请参阅 如何检测单元格格式的变化？

I will describe a workaround, almost step-by-step. It is not keystroke-by-keystroke, so you may have to google a bit, depending on you background knowledge. The description is not short, so please read it through.

我将描述一个解决方法，几乎是一步一步的。它不是逐个击键，因此您可能需要在谷歌上搜索一下，这取决于您的背景知识。 描述不短，所以请通读一遍。

You have to:

你必须：

1. Detect change in Selection (there is an event for this).
2. Inquire about the color of your source cell.
3. Act if needed.

1. 检测选择的变化（有一个事件）。
2. 查询源单元格的颜色。
3. 必要时采取行动。

Go to the Visual Basic Editor (VBE) and add code in three modules:

转到 Visual Basic 编辑器 (VBE) 并在三个模块中添加代码：

1. A standard module (say, Module1). You have to first insert the module.
2. ThisWorkbook.
3. Sheet2.

1. 一个标准模块（比如 Module1）。您必须先插入模块。
2. 本工作簿。
3. 表2。

In Module1:

在模块 1 中：

Public prev_sel As Range
Public wssrc As Worksheet, wstrg As Worksheet
Public ssrc As String, strg As String
Public rngsrc As Range, rngtrg As Range

Sub copy_color(rngs As Range, rngt As Range)
    Dim csrc As Long
    csrc = rngs.Interior.Color
    If (csrc = vbBlue) Then
        rngt.Interior.Color = vbBlue
    End If
End Sub

Sub copy_color2(rngs As Range, rngt As Range)
    If (TypeName(prev_sel) = "Range") Then
        Dim pss As String
        pss = prev_sel.Parent.Name
        If (pss = ssrc) Then
            Dim ints As Range
            Set ints = Application.Intersect(rngs, prev_sel)
            If (Not (ints Is Nothing)) Then
                Call copy_color(rngs, rngt)
            End If
        End If
    End If
End Sub

In ThisWorkbook:

在本工作簿中：

Private Sub Workbook_Open()
    ssrc = "Sheet2"
    strg = "Sheet1"
    Set wssrc = Worksheets(ssrc)
    Set wstrg = Worksheets(strg)
    Set rngsrc = wssrc.Range("A1")
    Set rngtrg = wstrg.Range("A1")
    Call copy_color(rngsrc, rngtrg)

    If (TypeName(Selection) = "Range") Then
        Set prev_sel = Selection
    Else
        Set prev_sel = Nothing
    End If
End Sub

In Sheet2:

在 Sheet2 中：

Private Sub Worksheet_Deactivate()
    Call copy_color(rngsrc, rngtrg)
    If (TypeName(Selection) = "Range") Then
        Set prev_sel = Selection
    Else
        Set prev_sel = Nothing
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call copy_color2(rngsrc, rngtrg)
    If (TypeName(Target) = "Range") Then
        Set prev_sel = Target
    End If
End Sub

I will soon edit with explanations. Reading carefully, it can be readily understood, though.

我将很快编辑解释。不过，仔细阅读，它很容易理解。

Notes:

笔记：

1. This code does not act if the source cell color changes from vbBlueto something else. You did not specify anything for this action. Actually, your specification was not detailed enough to cover all possible cases.

2. There might be cases (extremely unlikely, I guess) where this code fails. For instance, if color is changed via other VBA code, without selecting/deselecting cells.

3. The idea is to check for the need of acting after as many relevant events as possible. Here I am detecting Workbook_Open, Worksheet_Deactivate, Worksheet_SelectionChange. You may add other events with suitable Subs, e.g., Workbook_BeforeClose, Workbook_BeforeSave. All this is a way of substituting for the non-existing event of changing cell format.

4. I like the answer by pnuts(although I did not have time to test it). But the present one gives a flexibility that is not available with the other. There might be some cases (depending on what you need to do) that would not be covered by it.

5. There are other possible combinations of places to locate variables declaration and other code, essentially performing the same actions.

1. 如果源单元格颜色从更改vbBlue为其他颜色，则此代码不起作用。您没有为此操作指定任何内容。实际上，您的规范不够详细，无法涵盖所有​​可能的情况。

2. 可能存在此代码失败的情况（我猜是极不可能的）。例如，如果通过其他 VBA 代码更改颜色，而不选择/取消选择单元格。

3. 这个想法是在尽可能多的相关事件之后检查是否需要采取行动。我在这里检测Workbook_Open, Worksheet_Deactivate, Worksheet_SelectionChange。您可以使用合适的Subs添加其他事件，例如Workbook_BeforeClose, Workbook_BeforeSave。所有这些都是替代不存在的单元格格式更改事件的一种方式。

4. 我喜欢pnuts的答案（虽然我没有时间测试它）。但是目前的一种提供了另一种所没有的灵活性。可能有一些情况（取决于您需要做什么）不会被它涵盖。

5. 还有其他可能的位置组合来定位变量声明和其他代码，基本上执行相同的操作。

回答by pnuts

Not recommended because of reliance on the XLM (not XML) Macro function GET.CELL. This from a technology introduced 30 years ago that was effectively superseded eight years later. With almost all its elements now defunct, the few that remain can be expected to have a low life expectancy. Microsoftencourages migration to VBA.

不推荐，因为依赖 XLM（不是 XML）宏函数GET.CELL。这是 30 年前引入的一项技术，8 年后被有效取代。由于几乎所有元素现在都已不复存在，因此可以预期剩下的少数元素的预期寿命很短。Microsoft鼓励迁移到 VBA。

Nevertheless, you have asked ‘how' rather than ‘why not', so I suggest you proceed from where you have reached and select Sheet1 A1 and HOME > Styles - Conditional Formatting - New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:

尽管如此，您已经询问了“如何”而不是“为什么不”，因此我建议您从到达的位置继续并选择 Sheet1 A1 和 HOME > 样式 - 条件格式 - 新规则...，使用公式来确定哪些单元格格式化，格式化此公式为真的值：

=CellColor=23  

and select blue formatting of your choice, OK, OK, Apply.

并选择您选择的蓝色格式，确定，确定，应用。

23is a fairly standard number for Blue (not Light, not Dark) but your configuration may expect a different number.

23是蓝色（不是浅色，不是深色）的相当标准的数字，但您的配置可能需要不同的数字。

Note that another disadvantage is that, unlike CF in general, the response is not automatic – you may need to enter something in Sheet1 A1, or Shift+F9 to force an update.

请注意，另一个缺点是，与一般的 CF 不同，响应不是自动的——您可能需要在 Sheet1 A1 或 Shift+F9 中输入一些内容来强制更新。

---

---

If your data is spread across two sheets (Sheet1 and Sheet2, both ColumnA) and there is a 1:1 relationship (the p-value in A1 of Sheet2 is that for the correlation coefficient in A1 of Sheet1) then a simple Conditional Formatting rule may suffice:

如果您的数据分布在两个工作表（Sheet1 和 Sheet2，均为 ColumnA）并且存在 1:1 关系（Sheet2 的 A1 中的 p 值是 Sheet1 的 A1 中的相关系数的值），则使用简单的条件格式规则可能就足够了：

Select Sheet1 ColumnA and HOME > Styles - Conditional Formatting, New Rule...

选择 Sheet1 ColumnA 和 HOME > Styles - Conditional Formatting, New Rule...

Use a formula to determine which cells to format
Format values where this formula is true:

使用公式来确定要格式化哪些单元格
在此公式为真的情况下格式化值：

=Sheet2!A1>0.05

Format..., select dark blue or to suit, OK, OK.

Format...，选择深蓝色或适合，OK，OK。

The same rule might be applied in Sheet2 (ColumnA) in the same way so the cells (by row) conditionally formatted in one sheet are those conditionally formatted in the other.

可以以相同的方式在 Sheet2 (ColumnA) 中应用相同的规则，因此在一个工作表中有条件格式化的单元格（按行）是在另一个工作表中有条件格式化的单元格。

回答by barryleajo

The GET.CELL function, whilst useful, comes from the old XLM macro language which was used before VBA. You may encounter restrictions using this e.g. at that time Excel used a limited number of colours (I have read somewhere around 60?).

GET.CELL 函数虽然有用，但来自 VBA 之前使用的旧 XLM 宏语言。您可能会遇到使用它的限制，例如，当时 Excel 使用的颜色数量有限（我读过大约 60 种颜色？）。

Alternatively, with a bit of VBA, you could experiment with the Interior Objectand also the Font Object:

或者，使用一些 VBA，您可以尝试使用内部对象和字体对象：

Sheets("Sheet1").Range("A1").Interior.Color = vbBlue
Sheets("Sheet1").Range("A1").Font.Color = vbYellow
    If Sheets("Sheet1").Range("A1").Interior.Color = vbBlue Then _
    Sheets("Sheet2").Range("A1").Interior.Color = vbBlue
    If Sheets("Sheet1").Range("A1").Font.Color = vbYellow Then _
    Sheets("Sheet2").Range("A1").Font.Color = vbYellow

You will likely need to explore the various ways of specifying the colors to use to give you maximum control/flexibility.

您可能需要探索各种指定颜色的方法，以便为您提供最大程度的控制/灵活性。

回答by HarveyFrench

Just to be clear and to keep the functionality you deliver simple, you could use conditional formatting and choose to set the format with a colour. This is incredibly easy once you know how. The main trick is what formula to enter and specifically which cell you need a conditional formats formula to reference when the conditional format applies to a multi cell range.

为了清楚起见并保持您提供的功能简单，您可以使用条件格式并选择使用颜色设置格式。一旦您知道如何操作，这将非常容易。主要技巧是输入什么公式，特别是当条件格式应用于多单元格区域时，您需要条件格式公式来引用哪个单元格。

As an example. If your conditional formatting rule is created such that it applies to the range $C$5:$C$10 the formula you use will often need to be entered as =(A5="A"). Note this is a relative addressing formula ie no dollar signs. this has the effect of the cell c6 inspecting the value of a6 etc.

举个例子。如果您创建的条件格式规则适用于 $C$5:$C$10 范围，则您使用的公式通常需要输入为 =(A5="A")。请注意，这是一个相对寻址公式，即没有美元符号。这具有单元格 c6 检查 a6 等值的效果。

Your only complication now is to inspect the formatting of the cell rather than the value it stores. In 2013, you can still use =GET.CELL(63,A5)to do this, however this can't be entered in the formula of the CF rule ... Other posts discuss the whys and wherefores of using this. See this linkwhich described how to get cell info.

您现在唯一的麻烦是检查单元格的格式而不是它存储的值。在 2013 年，您仍然可以使用它=GET.CELL(63,A5)来执行此操作，但是无法将其输入到 CF 规则的公式中......其他帖子讨论了使用它的原因和原因。请参阅此链接，其中描述了如何获取单元格信息。

So you'll end up with a formula in a cell next to the cell that has the colouring. The formula will use a named range that returns true or false depending on whether the colour of the cell matches the colour you specify in the named range. You conditional formatting on another sheet will reference this formula cell and set the colour of the new cell.

因此，您将在具有颜色的单元格旁边的单元格中得到一个公式。该公式将使用一个命名范围，该范围返回 true 或 false，具体取决于单元格的颜色是否与您在命名范围中指定的颜色相匹配。您在另一张工作表上的条件格式将引用此公式单元格并设置新单元格的颜色。

You would use the following formula in the named range called "Get . =GET.CELL(65,OFFSET(INDIRECT("RC",FALSE),0,1))

您将在名为“Get . =GET.CELL(65,OFFSET(INDIRECT("RC",FALSE),0,1)) 的命名范围内使用以下公式

I've got this to work, and the key information can be found one the referenced web site page.

我已经让这个工作了，关键信息可以在引用的网站页面中找到。

Ok?

好？