为什么我的条件格式被VBA添加时被偏移？ [英] Why is my conditional format offset when added by VBA?

问题描述

如果表达式 =（$ G5<>）然后设置内部绿色，使用$ A $ 5：$ H $ 25。

尝试这个，按预期工作正常，然后尝试调整为VBA代码具有以下代码，该代码正在工作，但没有如预期的那样：

 使用ActiveSheet.UsedRange.Offset（1）
 .FormatConditions.Delete 
'将使用的行范围设置为绿色内部颜色，如果Erledigt Datum不为空
 With .FormatConditions.Add（Type：= xlExpression，_ 
 Formula1 ：==（$& cstrDefaultProgressColumn& _ 
 .row&"））
 .Interior.ColorIndex = 4 
结束
结束
  

问题是， .row 在调试时提供正确的行，但是我添加的条件公式似乎是一个或多个行关闭 - 取决于我的设置行的解决方案。所以我结束了一个条件格式，它有一个偏移的行，应该被格式化。

在对话框中，然后是$ code > =（$ G6 - ）或G3或G100310或类似的东西。但不是我想要的G5。

将行设置为动态，因为这用于在不同的工作表上设置条件格式，他们的数据从不同的行开始。

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

编辑：更具体地说，这是不一个UsedRange问​​题，与此同样的麻烦：

  Dim rngData As Range 
设置rngData = ActiveSheet.Range（A：H）'ActiveSheet.UsedRange .Offset（1）
 
 rngData.FormatConditions.Delete 
 
带有rngData.FormatConditions.Add（Type：= xlExpression，_ 
 Formula1：==（$ & cstrDefaultProgressColumn& _ 
 1&））
 .Interior.ColorIndex = 4 
结束
  

我的数据如下所示：

  1  - >空单元
 2  - >空单元
 3  - >空单元
 4  - > TitleCols  - > A; B; C; ...; H 
 5  - >数据到TitleCols 
。 。 
。 。 
。 。 
 25 
  

当我在Excel 2007上执行此编辑的代码，并在条件对话框是 =（$ G1048571<>） - 应该是 =（$ G1<>） $ c>，那么一切都可以正常工作。

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

编辑： 其他任务信息

我在这里使用条件格式，因为这个函数应该设置一个表对用户输入做出反应。所以，如果正确的设置和用户编辑了这个表格的条件列中的某个单元格，那么对应的行将会变成绿色的，用于所使用的行范围。

现在，因为在主标题行之前可能有行，并且可能会有各种数据列，而且目标列也可能会改变，我当然使用某些特定的信息。

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

GetTitleRow 用于通过NamedRange或标题内容确定标题行。

  With ActiveSheet.UsedRange.Offset（GetTitleRow（ActiveSheet.UsedRange） -  _ 
 ActiveSheet.UsedRange.Rows（1）.row + 1） 
  

更正了我的Formula1，因为我发现结构不太好。

  Formula1：==（& Cells（.row，_ 
 Range（strMatchCol1）.Column）.Address（RowAbsolute：= False ）& _ 
）
  

strMatchCol1 - 是范围的名称。

解决方案

得到它，哈哈。在执行grunt工作之前设置ActiveCell ...

  ActiveSheet.Range（A1）。激活
  

Excel正在拉扯自动范围调整，当添加了FromatCondition时，它将抛弃公式。

I was trying to add conditional formats like this:

If expression =($G5<>"") then make set interior green, use this for $A$5:$H$25.

Tried this, worked fine, as expected, then tried to adapt this as VBA-Code with following code, which is working, but not as expected:

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 is providing the right row while in debug, however my added conditional-formula seems to be one or more rows off - depending on my solution for setting the row. So I am ending up with a conditional formatting, which has an offset to the row, which should have been formatted.

In the dialog it is then =($G6<>"") or G3 or G100310 or something like this. But not my desired G5.

Setting the row has to be dynamicall, because this is used to setup conditional formats on different worksheets, which can have their data starting at different rows.

I was suspecting my With arrangement, but it did not fix this problem.

edit: To be more specific, this is NOT a UsedRange problem, having the same trouble with this:

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

My Data looks like this:

1 -> empty cells
2 -> empty cells
3 -> empty cells
4 -> TitleCols -> A;B;C;...;H
5 -> Data to TitleCols
. .
. .
. .
25

When I execute this edited code on Excel 2007 and lookup the formula in the conditional dialog it is =($G1048571<>"") - it should be =($G1<>""), then everything works fine.

Whats even more strange - this is an edited version of a fine working code, which used to add conditional formats for each row. But then I realized, that it's possible to write an expression, which formats a whole row or parts of it - thought this would be adapted in a minute, and now this ^^

edit: Additional task informations

I use conditional formatting here, because this functions shall setup a table to react on user input. So, if properly setup and a user edits some cell in my conditionalized column of this tabel, the corresponding row will turn green for the used range of rows.

Now, because there might be rows before the main header-row and there might be a various number of data-columns, and also the targeted column may change, I do of course use some specific informations.

To keep them minimal, I do use NamedRanges to determine the correct offset and to determine the correct DefaultProgessColumn.

GetTitleRow is used to determine the header-row by NamedRange or header-contents.

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

Corrected my Formula1, because I found the construct before not well formed.

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

strMatchCol1 - is the name of a range.

解决方案

Got it, lol. Set the ActiveCell before doing the grunt work...

ActiveSheet.Range("A1").Activate

Excel is pulling its automagic range adjusting which is throwing off the formula when the FromatCondition is added.

这篇关于为什么我的条件格式被VBA添加时被偏移？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！