我尝试添加条件格式是这样的：
如果表达式=($G5<>"")然后使设置内部绿色，用这个$ A $ 5：$ H $ 25。
试过了，工作得很好，符合市场预期，然后试着用下面的代码，这是工作，以适应这是VBA的代码，但并不如预期：
With ActiveSheet.UsedRange.Offset(1)
.FormatConditions.Delete
'set used row range to green interior color, if "Erledigt Datum" is not empty
With .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=($" & cstrDefaultProgressColumn & _
.row & "<>"""")")
.Interior.ColorIndex = 4
End With
End With
问题是 ， .row根据我设置的行的解决方案-是同时调试，但是我加入条件，公式似乎是一个或多个行关闭提供正确的行。 因此，我结束了一个条件格式，这已经偏移到行，应该已被格式化。
在对话框它然后=($G6<>"")或G3或G100310或这样的事情。 但不是我想要的G5。
设置该行必须是dynamicall，因为这是用来设置条件格式在不同的工作表，它可以有自己的数据开始在不同的行。
我怀疑我With安排，但它并没有解决这个问题。
编辑：更具体，这不是 UsedRange问题，有这个同样的问题：
Dim rngData As Range
Set rngData = ActiveSheet.Range("A:H") 'ActiveSheet.UsedRange.Offset(1)
rngData.FormatConditions.Delete
With rngData.FormatConditions.Add(Type:=xlExpression, _
Formula1:="=($" & cstrDefaultProgressColumn & _
1 & "<>"""")")
.Interior.ColorIndex = 4
End With
我的数据是这样的：
1 -> empty cells
2 -> empty cells
3 -> empty cells
4 -> TitleCols -> A;B;C;...;H
5 -> Data to TitleCols
. .
. .
. .
25
当我在Excel 2007中执行此编辑的代码和查找公式中的条件对话框中，它是=($G1048571<>"") -它应该是=($G1<>"")然后一切工作正常。
什么更奇怪 - 这是一个精细的工作代码，用来添加条件格式为每行一个编辑的版本。 但后来我意识到，它可以编写一个表达式，其格式整行或部分 - 认为这将是适合在一分钟内，现在这个^^
编辑： 额外任务信息
我在这里使用条件格式，因为这种功能应建立一个表对用户的输入做出反应。 所以，如果正确安装和用户编辑我这个TABEL的条件化列中的某些单元格，相应的行会变绿行的使用范围。
现在，因为有可能是行的主头排前，可能有各种数字数据列的，也是有针对性的列可能会改变，我当然可以用一些特定的信息。
为了让他们最小的，我使用NamedRanges以确定正确的偏移，并确定正确的DefaultProgessColumn 。
GetTitleRow用于通过NamedRange或报头的内容来确定所述标头行。
With ActiveSheet.UsedRange.Offset(GetTitleRow(ActiveSheet.UsedRange) - _
ActiveSheet.UsedRange.Rows(1).row + 1)
更正我的一级方程式，因为我发现之前没有很好地形成的构造。
Formula1:="=(" & Cells(.row, _
Range(strMatchCol1).Column).Address(RowAbsolute:=False) & _
"<>"""")"
strMatchCol1 -是一个范围的名称。
明白了，哈哈。 做繁重的工作之前，设置ActiveCell ...
ActiveSheet.Range("A1").Activate
的Excel是拉动添加FromatCondition当其被摆脱式其AUTOMAGIC范围调整。
其原因是，条件格式和数据验证出这种奇怪的行为是因为他们使用的公式是正常的计算外链。 他们必须让你可以参考公式中的活动单元格。 如果您在G1的时候，你不能键入=G1=""因为你要创建一个循环引用。 但是，在CF或DV，您可以键入公式。 这些公式与解除关联不同于真正的公式当前单元格。
当你输入一个CF公式，它总是相对于活动单元格。 如果在CF，你犯了一个公式
=ISBLANK($G2)
你在A5是时，Excel将其转换为
=ISBLANK(R[-3]C7)
而当被装进CF，它最终是相对于它的应用到单元格。 因此，在第2行，公式就出来
=ISBLANK($G655536)
（对于Excel 2003）。 它弥补-3行和换到电子表格的底部。
您可以使用Application.ConvertFormula使公式相对于其他一些细胞中。 如果我在第5行和我的范围的开始是在第2行，我制备式相对于到行8这样将R [-3]将使式中A5为$ G5（三行向上从A8 ）。
Sub test()
Dim cstrDefaultProgressColumn As String
Dim sFormula As String
cstrDefaultProgressColumn = "$G"
With ActiveSheet.UsedRange.Offset(1)
.FormatConditions.Delete
'set used row range to green interior color, if "Erledigt Datum" is not empty
'Build formula
sFormula = "=ISBLANK(" & cstrDefaultProgressColumn & .Row & ")"
'convert to r1c1
sFormula = Application.ConvertFormula(sFormula, xlA1, xlR1C1)
'convert to a1 and make relative
sFormula = Application.ConvertFormula(sFormula, xlR1C1, xlA1, , ActiveCell.Offset(ActiveCell.Row - .Cells(1).Row))
With .FormatConditions.Add(Type:=xlExpression, _
Formula1:=sFormula)
.Interior.ColorIndex = 4
End With
End With
End Sub
我只偏移.Cells(1)在行因为该列是在这个例子中是绝对的。 如果行和列是相对的CF公式中，你需要更多的抵消。 此外，如果活动单元格在你的范围内第一个单元格下面这仅适用。 为了使它更通用的，你必须确定其中activecell是相对于范围，并适当地抵消。 如果偏移把你上面第1行，你需要编写代码，以便它称为一个细胞较近行总数的底部，您的Excel版本。
如果你认为选择是一个有点杂牌的，我相信你会同意，这是雪上加霜。 尽管我痛恨不必要的选择和激活，条件格式和数据验证两个地方它是一个必要之恶。
A brief example:
Sub Format_Range()
Dim oRange As Range
Dim iRange_Rows As Integer
Dim iCnt As Integer
'First, create a named range manually in Excel (eg. "FORMAT_RANGE")
'In your case that would be range "$A$5:$H$25".
'You only need to do this once,
'through VBA you can afterwards dynamically adapt size + location at any time.
'If you don't feel comfortable with that, you can create headers
'and look for the headers dynamically in the sheet to retrieve
'their position dynamically too.
'Setting this range makes it independent
'from which sheet in the workbook is active
'No unnecessary .Activate is needed and certainly no hard coded "A1" cell.
'(which makes it more potentially subject to bugs later on)
Set oRange = ThisWorkbook.Names("FORMAT_RANGE").RefersToRange
iRange_Rows = oRange.Rows.Count
For iCnt = 1 To iRange_Rows
If oRange(iCnt, 1) <> oRange(iCnt, 2) Then
oRange(iCnt, 2).Interior.ColorIndex = 4
End If
Next iCnt
End Sub
Regarding my comments given on the other reply:
If you have to do this for many rows, it is definitely faster to load the the entire range into memory (an array) and check the conditions within the array, after which you do the writing on those cells that need to be written (formatted).
I could agree that this technique is not "necessary" in this case - however it is good practise because it is flexible for many (any type of) customizations afterwards and easier to debug (using the immediate / locals / watches window).
I'm not a fan of Offset although I don't state it doesn't work as it should and in some limited scenarios I could say that the chance for problems "could" be small: I experienced that some business users tend to use it constantly (here offset +3, there offset -3, then again -2, etc...); although it is easy to write, I can tell you it is hell to revise. It is also very often subject to bugs when changes are made by end users.
I am very much "for" the use of headers (although I'm also a fan of reducing database capabilities for Excel, because for many it results in avoiding Access), because it will allow you very much flexibility. Even when I used columns 1 and 2; better is it to retrieve the column nr dynamically based on the location of the named range of the header. If then another column is inserted, no bugs will appear.
Last but not least, it may sound exaggerated, but the last time, I used a class module with properties and functions to perform all retrievals of potential data within each sheet dynamically, perform checks on all bugs I could think of and some additional functions to execute specific tasks.
So if you need many types of data from a specific sheet, you can instantiate that class and have all the data at your disposal, accessible through defined functions. I haven't noticed anyone doing it so far, but it gives you few trouble despite a little bit more work (you can use the same principles again over and over).
Now I don't think that this is what you need; but there may come a day that you need to make large tools for end users who don't know how it works but will complain a lot about things because of something they might have done themselves (even when it's not your "fault"); it's good to keep this in mind.
