使用Worksheet_SelectionChange重写条件格式

I have some code in a worksheet to highlight the row of a selected cell. However, this sheet has conditional formatting which colours some of the rows. The highlighting macro does not work on the rows where the formatting condition is being met (in the sense that the colour of the highlight is not overriding the colour of the conditional formatting).

我在工作表中有一些代码来突出显示所选单元的行。但是，这个表有条件格式，可以对某些行进行着色。突出显示宏不能在满足格式条件的行上工作(从突出显示的颜色不覆盖条件格式的颜色来看)。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Cells.Interior.ColorIndex = xlColorIndexNone 
    ActiveCell.EntireRow.Interior.ColorIndex = 19 'Highlight row
End Sub

Does anyone know a way around this without removing the conditional formatting? Eg. can I temporarily disable it for a selected row and re-enable it when the row is unselected?

有没有人知道一种不用删除条件格式的方法?如。我是否可以暂时为选定的行禁用它，并在未选中的行时重新启用它?

The formatting is one rule applied to all cells. I figure in theory I could create an independent rule for every row (~500 of them) and then turn that off completely and later reapply it but that seems a little overboard.

格式化是应用于所有单元格的一条规则。我认为理论上我可以为每一行(大约500行)创建一个独立的规则，然后完全关闭它，然后重新应用它，但这似乎有点过头了。

1 个解决方案

#1

There is no need to use ActiveCell in your Worksheet_SelectionChange event macro. That is what Target is/does.

在您的Worksheet_SelectionChange事件宏中不需要使用ActiveCell。这就是目标。

Modify your Worksheet_SelectionChange to be closer to the following.

修改您的Worksheet_SelectionChange，使其更接近以下内容。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Name = "mySelection"
    Cells.Interior.Pattern = xlNone
    Target.EntireRow.Interior.ColorIndex = 19
End Sub

Now you will be constantly redefining a named range for each new selection of cells.

现在，您将不断地为每个新的单元格选择重新定义一个命名范围。

You didn't disclose what the CF rule actually was so I'm going to assume that is highlights cell that are not blank. Modify the existing CF rule to be of the Use a formula to determine which cells to format variety and adjust the following to suit your own CF rule then put it in the Format values where this formula is true: text box.

你没有披露CF规则实际上是什么所以我假设它是高亮显示单元格，不是空白的。修改现有的CF规则，使之成为使用公式来确定要格式化哪些单元格，并调整以下内容以适应您自己的CF规则，然后将其放入公式为真的格式值:文本框。

=AND(A1<>"", ROW(A1)<>ROW(mySelection))

By adding a boolean criteria within an AND function and constantly redefining the mySelection range to the current selection you can override the CF rule's formatting.

通过在一个和函数中添加一个布尔标准，并不断地重新定义当前选择的mySelection范围，您可以重写CF规则的格式。