将3色比例应用于Excel 2010中的整个行。 [英] Apply 3-color scale to an entire row in Excel 2010.

问题描述

我在MS Excel 2010中有一个表。该表有两列。第一列是一个人的名字（Col A），第二列是该人在考试中获得的分数（Col B）。

I have an table in an MS Excel 2010. The table has two columns. The first column is a name of a person (Col A), the second column is the marks that the person secured in an exam (Col B).

我正在应用条件格式。如果我选择以下向导

I am applying conditional formatting. If I choose the following wizard

Home > Conditional Formatting > Format all cells based on their values  

我可以用3色标对Col B进行着色。这正是我想要的。但是，我希望将其用于整个行，而不仅限于Col B中的单元格。我还希望该名称的格式应与标记的颜色相同。

I can color the Col B on a 3-color scale. This is exactly what I want. However, I want it for the entire row and not only the cell in Col B. I want the name also to be formatted in the same color as the marks.

有人知道该怎么做吗？

Anyone knows how to do this?

我已经四处看看了。以下内容无法解决我要解决的特定问题。

I have already looked around a bit. The following came close to did not solve the particular problem that I am trying to.

1. > http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional -formatting /


2. 条件格式基于日期的行

1. http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/
2. Conditional Formatting Rows Based on Date

推荐答案

您可能必须使用VBA代码。

You're probably going to have to use VBA code for this.

右键单击工作表标签，然后选择查看代码

Right click the worksheet label and select 'View Code'

在代码窗口内，然后粘贴以下代码：

Inside the code window, paste in the following code:

Sub RunMe()

Dim xRng As Range, xCell As Range

With Me

    Set xRng = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
    ' Change the first '2' above to reflect the starting row of your data

    For Each xCell In xRng
        xCell.Offset(0, -1).Interior.Color = xCell.DisplayFormat.Interior.Color
    Next xCell

End With

End Sub

现在，每次您运行宏（Alt-F8，选择宏）时，列A的格式将与条件

Now every time you run the macro (Alt-F8, select macro), column A will be formatted with the conditional formatting assigned to column B.

如果希望此过程是自动的，请更改：

If you want this process to be automatic, change:

Sub RunMe()

类似于：

Private Sub Worksheet_Activate()
' This will run the macro whenever the worksheet is selected

，也可以将代码分配给键盘快捷键或命令按钮等。

or you could assign the code to a keyboard shortcut or a command button etc.

例如每次打开文件时都会运行的代码，保存为关闭状态等，请将代码添加到ThisWorkbook代码窗口中（尽管您必须对代码进行一些更改，因为 Me是指代码所在的特定工作表放置）。

If you would like the code to run every time the file is opened, saved closed etc, add the code instead to the ThisWorkbook code window (although you'd have to alter the code slightly as 'Me' is referencing the particular worksheet in which the code is placed).

这篇关于将3色比例应用于Excel 2010中的整个行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！