首页 文章

为何我的条件格式偏移由VBA添加？

提问于
浏览 次
2

我试图添加这样的条件格式：

如果表达式 =($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

The Problem is ， .row 在调试时提供了正确的行，但是我添加的条件公式似乎是一行或多行 - 取决于我设置行的解决方案 . 所以我最终得到一个条件格式，它有一个行的偏移量，应该已格式化 .

在对话框中，它是 =($G6<>"") 或G3或G100310或类似的东西 . 但不是我想要的G5 .

设置行必须是动态的，因为这用于在不同的工作表上设置条件格式，这些工作表的数据可以从不同的行开始 .

我怀疑我的 With 安排，但它没有解决这个问题 .

edit: 更具体地说，这是 NOT 一个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<>"") ，然后一切正常 .

更奇怪的是 - 这是一个精细工作代码的编辑版本，用于为每一行添加条件格式 . 但后来我意识到，有可能写一个表达式，它可以格式化整行或部分表达式 - 认为这会在一分钟后进行调整，现在这个^^

edit: 其他任务信息

我在这里使用条件格式，因为这个函数应该设置一个表来响应用户输入 . 因此，如果正确设置并且用户在此表格的条件化列中编辑了某个单元格，则相应的行将对所使用的行范围变为绿色 .

现在，因为在主 Headers 行之前可能存在行，并且可能存在不同数量的数据列，并且目标列也可能发生变化，我当然会使用一些特定的信息 .

为了保持最小化，我确实使用NamedRanges来确定正确的偏移并确定正确的 DefaultProgessColumn .

GetTitleRow 用于通过NamedRange或header-contents确定 Headers 行 .

With ActiveSheet.UsedRange.Offset(GetTitleRow(ActiveSheet.UsedRange) - _
                                ActiveSheet.UsedRange.Rows(1).row + 1)

纠正了我的Formula1，因为我发现之前没有很好的构造 .

Formula1:="=(" & Cells(.row, _
           Range(strMatchCol1).Column).Address(RowAbsolute:=False) & _
           "<>"""")"

strMatchCol1 - 是范围的名称 .

excel vba excel-vba conditional-formatting

3 回答

  • 3

    明白了，哈哈 . 在进行咕噜声工作之前设置ActiveCell ......

    ActiveSheet.Range("A1").Activate

    Excel正在拉动其自动调整范围，这在添加FromatCondition时会丢掉公式 .

    回复于
  • 4

    条件格式和数据验证表现出这种奇怪行为的原因是因为它们使用的公式超出了正常的计算链 . 它们必须能够引用公式中的活动单元格 . 如果您're in G1, you can' t键入 =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版本总行数底部的单元格 .

    如果你认为选择有点像kludge，我相信你会同意这更糟糕 . 即使我厌恶不必要的选择和激活，条件格式和数据验证是两个必要的恶魔 .

    回复于
  • 1

    一个简短的例子：

    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

    关于我对其他答复的评论：

    如果必须对很多行执行此操作，将整个范围加载到内存（数组）中并检查数组中的条件肯定会更快，之后您将对需要写入的单元格进行写入（格式化） ） .
    我同意这种技术在这种情况下不是"necessary" - 但这是一种很好的做法，因为它对于许多（任何类型的）自定义都很灵活，之后更容易调试（使用immediate / locals / watches窗口） .
    我认为它不能正常工作，并且在一些有限的情况下，我可以说问题的机会很小：我经历过一些商业用户倾向于不断使用它（这里偏移3，偏移-3，然后再-2，等等......）;虽然写起来很容易，但我可以告诉你，修改是很难的 . 当最终用户进行更改时，它也经常遇到错误 .
    我非常喜欢使用 Headers （虽然我也是减少Excel数据库功能的粉丝，因为对于很多人来说它会导致避免访问），因为它会让你非常灵活 . 即使我使用第1列和第2列;最好是根据 Headers 的命名范围的位置动态检索列nr . 如果然后插入另一列，则不会出现错误 .

    最后但并非最不重要的是，它可能听起来有点夸张，但最后一次，我使用了一个带有属性和函数的类模块来动态地执行每个工作表中潜在数据的所有检索，对我能想到的所有错误执行检查，并执行一些其他功能 . 执行特定任务 .
    因此，如果您需要来自特定工作表的多种类型的数据，您可以实例化该类并拥有您可以使用的所有数据，可通过定义的函数访问 . 到目前为止，我还没有注意到有人这么做，但是尽管做了一些工作，它给你带来了一些麻烦（你可以一遍又一遍地使用相同的原理） .
    现在我不知道它是如何运作的，但会因为他们可能已经完成的事情而抱怨很多东西（即使它很好地记住了这一点 . ）

    回复于

相关问题