如何将Excel 2010中的条件格式复制到基于其他单元格内容的其他单元格中?

I need to copy a formula based conditional formatting to other cells but i have to change the formula for every single cell condition. how can I do a copy of this condition so that the formula changes accordingly as well? in cells, when copy cells with formula referring to other cells, then excel will change the referring cells accordingly. But I don't know how to do the same in conditional formatting so that I don't need to change my conditional formula manually:

我需要将基于条件格式的公式复制到其他单元格中，但我必须针对每个单元格条件更改公式。我怎么做这个条件的副本，这样公式也会相应地改变?在单元格中，当使用引用其他单元格的公式复制单元格时，excel将相应地更改引用单元格。但是我不知道如何在条件格式中做同样的事情所以我不需要手动修改条件公式:

condition: =K21+$F22 (the conditional formula which I put in cell K22) condition: =AB21+$F22 (the conditional formula which I need to be in cell AB22)

条件:=K21+$F22(我代入K22单元格的条件公式)条件:=AB21+$F22(我需要代入AB22单元格的条件公式)

As I have more than 10000 cells to apply my conditional formatting formula, it's almost impossible to type this formula manually in every single cell. Is there a way to do it automatically?

当我有超过10000个单元格来应用我的条件格式公式时，几乎不可能在每一个单元格中手动输入这个公式。有自动完成的方法吗?

I don't know VB but I can learn if the solution is there only. But please give me no abbreviation and explain me completely.

我不知道VB，但我可以知道是否只有解。但是请不要给我任何缩写和完整的解释。

Thanks a lot! Shahab

谢谢!该导弹

5 个解决方案

#1

I ran into the same situation where when I copied the formula to another cell the formula was still referencing the cell used in the first formula. To correct this when you set up the rules, select the option "use a formula to determine which cells to format. Then type in the box your formula, for example H23*.25. When you copy the cells down the formulas will change to H24*.25, H25*.25 and so on. Hope this helps.

我遇到了同样的情况，当我将公式复制到另一个单元时，公式仍然引用了第一个公式中使用的单元格。要在设置规则时纠正此错误，请选择“使用公式确定要格式化哪些单元格”。然后输入公式，例如H23*.25。当你把单元格复制下来，公式就会变成H24*。25日,H25 *。25日等等。希望这个有帮助。

#2

You can do this in the 'Conditional Formatting' tool in the Home tab of Excel 2010.

您可以在excel2010的Home选项卡中的“条件格式”工具中进行此操作。

Assuming the existing rule is 'Use a formula to dtermine which cells to format':

假设现有规则是“使用公式来确定要格式化哪些单元格”:

Edit the existing rule, so that the 'Formula' refers to relative rows and columns (i.e. remove $s), and then in the 'Applies to' box, click the icon to make the sheet current and select the cells you want the formatting to apply to (absolute cell references are ok here), then go back to the tool panel and click Apply.

编辑现有的规则,这样“公式”是指相对的行和列(即删除年代美元),然后在“适用于”框中,单击图标,使当前表并选择你想要的细胞格式申请(绝对细胞引用在这里好),然后回到工具面板,单击apply。

This will work assuming the relative offsets are appropriate throughout your desired apply-to range.

假设相对偏移量在整个应用程序到范围中都是合适的，那么这将会起作用。

You can copy conditional formatting from one cell to another or a range using copy and paste-special with formatting only, assuming you do not mind copying the normal formats.

您可以将条件格式从一个单元格复制到另一个单元格，或者使用copy和pastespecial惟一的格式，假设您不介意复制常规格式。

#3

condition: =K21+$F22

条件:= + K21——F22美元

That is not a CONDITION. That is a VALUE. A CONDITION, evaluates as a BOOLEAN value (True/False) If True, then the format is applied.

这不是条件。这是一个价值。如果条件为真，则计算为布尔值(True/False)，然后应用格式。

This would be a CONDITION, for instance

例如，这将是一个条件。

condition: =K21+$F22>0

In general, when applying a CF to a range,

一般来说，当把CF应用到一个范围时，

1) select the entire range that you want the Conditional FORMAT to be applied to.

2) enter the CONDITION, as it relates to the FIRST ROW of your selection.

The CF accordingly will be applied thru the range.

因此CF将通过范围应用。

#4

I, too, have need for this! My situation involves comparing actuals with budget for cost centers, where expenses may have been mis-applied and therefore need to be re-allocated to the correct cost center so as to match how they were budgeted. It is very time consuming to try and scan row-by-row to see if each expense item has been correctly allocated. I decided that I should apply conditional formatting to highlight any cells where the actuals did not match the budget. I set up the conditional formatting to change the background color if the actual amount under the cost center did not match the budgeted amount.

我也需要这个!我的情况是，将实际情况与成本中心的预算进行比较，在那里，费用可能被错误地应用，因此需要重新分配给正确的成本中心，以匹配他们的预算。尝试并逐行扫描以查看每个开销项是否被正确分配，这非常耗时。我决定应用条件格式来突出显示任何实际值与预算不匹配的单元格。如果成本中心下的实际金额与预算金额不匹配，我设置条件格式来更改背景颜色。

Here's what I did:

这是我所做的:

Start in cell A1 (or the first cell you want to have the formatting). Open the Conditional Formatting dialogue box and select Apply formatting based on a formula. Then, I wrote a formula to compare one cell to another to see if they match:

从单元格A1(或您想要格式化的第一个单元格)开始。打开条件格式对话框，根据公式选择应用格式。然后，我写了一个公式来比较一个细胞和另一个细胞，看它们是否匹配:

=A1=A50

If the contents of cells A1 and A50 are equal, the conditional formatting will be applied. NOTICE: no $$, so the cell references are RELATIVE! Therefore, you can copy the formula from cell A1 and PasteSpecial (format). If you only click on the cells that you reference as you write your conditional formatting formula, the cells are by default locked, so then you wouldn't be able to apply them anywhere else (you would have to write out a new rule for each line- YUK!)

如果单元格A1和A50的内容相等，则应用条件格式。注意:没有$，所以单元格引用是相对的!因此，您可以从单元A1和特殊(格式)复制公式。如果您在编写条件格式公式时只单击所引用的单元格，那么单元格将被默认锁定，因此您将无法在其他任何地方应用它们(您必须为每一行编写一个新规则——YUK!)

What is really cool about this is that if you insert rows under the conditionally formatted cell, the conditional formatting will be applied to the inserted rows as well!

真正有趣的是，如果您在条件格式化的单元格下插入行，条件格式也将应用到所插入的行中!

Something else you could also do with this: Use ISBLANK if the amounts are not going to be exact matches, but you want to see if there are expenses showing up in columns where there are no budgeted amounts (i.e., BLANK) .

您还可以做一些其他的事情:如果金额不匹配，请使用ISBLANK，但是您想看看是否有支出出现在没有预算金额的列中(例如:,空白)。

This has been a real time-saver for me. Give it a try and enjoy!

这对我来说是一个节省时间的方法。试试看吧!

#5

I had this problem too and couldn't solve it without using VBA.

我也有这个问题，没有VBA我无法解决。

In my case I had a table with numbers that I wanted to be formatted and a corresponding table next to it with the desired formatting values.

在我的例子中，我有一个表，其中包含我想要格式化的数字，还有一个相应的表，它旁边有所需的格式化值。

i.e. While column F contains the values I want to format, the desired formatting for each cell is captured in column Z, expressed as "RED", "AMBER" or "GREEN."

例如，当列F包含我想要格式化的值时，每个单元格所需的格式在列Z中被捕获，表示为“红色”、“琥珀”或“绿色”。

Quick solution below. Manually select the range to which to apply the conditional formatting and then run the macro.

下面的快速解决方案。手动选择要应用条件格式的范围，然后运行宏。

   Sub ConditionalFormatting()
For Each Cell In Selection.Cells
With Cell
    'clean
    .FormatConditions.Delete

    'green rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""GREEN"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False

    'amber rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""AMBER"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
    .FormatConditions(1).StopIfTrue = False

    'red rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""RED"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

Next Cell

End Sub

#1

#2