Excel 2010 VBA：设置上一列中单元格引用单元格的颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5221559/
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
Excel 2010 VBA: Set the color of a cell reference cell in previous column
提问by John McArthur
I have macro that populates a sheet, and copies values into a grid. I want to add conditional formatting to the cells, so that when they are updated if they become greater than the previous column they turn red, smaller they turn green and become 0 they turn grey.
我有填充工作表的宏，并将值复制到网格中。我想为单元格添加条件格式，这样当它们更新时，如果它们变得大于前一列，它们会变成红色，更小它们会变成绿色，变成 0，它们会变成灰色。
My problem is adding the condition and referenceing the cell.
我的问题是添加条件并引用单元格。
Set newCell = originalEstimateCell.Offset(0, 2 + i)
newCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="XX"
newCell.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
My question is what replaces the XX to refer to the previous column in the same row, ie. if we are in Cell C7 I want to refer to cell B7.
我的问题是什么取代了 XX 来引用同一行中的前一列，即。如果我们在单元格 C7 中，我想引用单元格 B7。
Thanks
谢谢
回答by MP?kalski
Try this one
试试这个
Sub test()
ActiveCell.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" & ActiveCell.Offset(0, -1).Address()
ActiveCell.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub
The code above states that the current cell value should be greater than the value of the Formula1, and the formula itself is the address of the cell one column before the active cell. If you want some other cell to be formatted, relatively to the active cell, just change the paramters of the offset method.
上面的代码说明当前单元格的值应该大于Formula1的值，而公式本身就是活动单元格前一列的单元格地址。如果您希望设置其他单元格的格式，相对于活动单元格，只需更改偏移方法的参数即可。
I hope it helped.
我希望它有所帮助。
回答by Mark Baker
My question is what replaces the XX to refer to the previous column in the same row, ie. if we are in Cell C7 I want to refer to cell C6.
我的问题是什么取代了 XX 来引用同一行中的前一列，即。如果我们在单元格 C7 中，我想引用单元格 C6。
C6 would be the previous rowto C7, not the previous column
C6 将是C7的前一行，而不是前一列
Try
尝试
=INDIRECT("C" & ROW()-1)
to get the value from the previous row
从前一行获取值
