vba 如何使用excel宏将条件格式应用于多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10292921/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to apply the conditional formatting to multiple columns using excel macro
提问by David Kin
I created a conditional formatting to change the color based on value compare for one cell, but I don't know how to apply this formatting for the whole column and even other columns? (do I have to use for loop to set the formatting for all the cells?)
我创建了一个条件格式来根据一个单元格的值比较来更改颜色，但我不知道如何将此格式应用于整列甚至其他列？（我是否必须使用 for 循环来设置所有单元格的格式？）
'add conditionalFormating for one cell.
Set rngCell = Cells(6, 7)
Set objCF = rngCell.FormatConditions.Add _
(Type:=xlExpression, _
Formula1:="=" & rngCell.Address & " > " & rngCell.offset(, -3).Address)
'set formats for new CF
With objCF
.Font.ColorIndex = 26
.Interior.ColorIndex = 19
End With
Thanks in advance
提前致谢
采纳答案by Ed Bolton
Unfortunately, Excel 2007/2010 don't have as extensive a macro record function as earlier versions
不幸的是，Excel 2007/2010 没有像早期版本那样广泛的宏记录功能
If you are referencing other cells when applying conditional formatting, a good method is to apply it to one cell (which you know how to do), and then copy the formatting to the rest of the column; if you are filling down a column, this should not cause you to lose other cell formats you may wish to keep
如果您在应用条件格式时引用其他单元格，一个好的方法是将其应用到一个单元格（您知道如何操作），然后将格式复制到列的其余部分；如果您正在填写一列，这不应导致您丢失您可能希望保留的其他单元格格式
So I would start your code with
所以我会开始你的代码
rngCell.FormatConditions.Delete
Then once the format is added, you can simply use:
然后一旦添加了格式，您就可以简单地使用：
rngCell.Copy
rngOut.PasteSpecial xlPasteFormats
, where rngOut is defined as starting with rngCell and filling down to the last row in the table
，其中 rngOut 定义为以 rngCell 开头并向下填充到表中的最后一行
To apply to other columns, you would probably need a different formula as there are different offsets. To minimise the required code, you could always manually add the full set of formats/conditional formats you want to a hidden row just above the table's headers. Then you can copy all of these to your table using code of the form...
要应用于其他列，您可能需要不同的公式，因为有不同的偏移量。为了最小化所需的代码，您始终可以手动将您想要的全套格式/条件格式添加到表格标题正上方的隐藏行中。然后您可以使用表单代码将所有这些复制到您的表格中...
Range("A1:J1").Copy
Range("A3:J100").PasteSpecial xlPasteFormats
...if we assume your header gets pushed down to row 2 when you add the formatted row in row 1
...如果我们假设您在第 1 行添加格式化行时将标题下推到第 2 行
Although I have referred to cell addresses in the above example, I would always recommend referring to range names and using cells notation e.g. Range("A1") is Cells(1, 1). The use of range names to define columns in tables is covered in more detail in this expert Excel video. The benefits are immense...if your header row is defined with a range name, you can insert a new row above your table without having to re-write any code
虽然我在上面的例子中提到了单元格地址，但我总是建议参考范围名称并使用单元格符号，例如 Range("A1") is Cells(1, 1)。此Excel 专家视频更详细地介绍了使用范围名称定义表格中的列。好处是巨大的...如果您的标题行使用范围名称定义，您可以在表格上方插入一个新行而无需重新编写任何代码
