在不使用 VBA 的情况下突出显示 Excel 中的活动行/列？

Highlight active row/column in Excel without using VBA?

提问by lowak

What I want to achieve is to highlight active row or column. I used VBA solutions but everytime Selection_changeevent is used I am loosing chance to undo any changes in my worksheet.

我想要实现的是突出显示活动行或列。我使用了 VBA 解决方案，但每次使用Selection_change事件时，我都失去了撤消工作表中任何更改的机会。

Is there a way to somehow highlight active row / column without using VBA?

有没有办法在不使用 VBA 的情况下以某种方式突出显示活动行/列？

采纳答案by e.James

I don't think it can be done without using VBA, but it canbe done without losing your undo history:

我认为不使用 VBA就无法完成，但可以在不丢失撤消历史记录的情况下完成：

In VBA, add the following to your worksheet object:

在 VBA 中，将以下内容添加到您的工作表对象中：

Public SelectedRow as Integer
Public SelectedCol as Integer

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    SelectedRow = Target.Row
    SelectedCol = Target.Column
    Application.CalculateFull ''// this forces all formulas to update
End Sub

Create a new VBA module and add the following:

创建一个新的 VBA 模块并添加以下内容：

Public function HighlightSelection(ByVal Target as Range) as Boolean
    HighlightSelection = (Target.Row = Sheet1.SelectedRow) Or _
        (Target.Column = Sheet1.SelectedCol)
End Function

Finally, use conditional formatting to highlight cells based on the 'HighlightSelection' formula:

最后，使用条件格式根据“HighlightSelection”公式突出显示单元格：

回答by hstay

The best you can get is using conditional Formatting.

你能得到的最好的方法是使用条件格式。

Create two formula based rules:

创建两个基于公式的规则：

1. =ROW()=CELL("row")
2. =COLUMN()=CELL("col")

1. =ROW()=CELL("row")
2. =COLUMN()=CELL("col")

As shown in:

如图所示：

The only drawback is that every time you select a cell you need to recalculate your sheet. (You can press "F9")

唯一的缺点是每次选择单元格时都需要重新计算工作表。（你可以按“F9”）

回答by Aaron Wallentine

You can temporarily highlight the current row (without changing the selection) by pressing Shift+Space. Current column with Ctrl+Space.

您可以通过按 暂时突出显示当前行（不更改选择）Shift+Space。带有 的当前列Ctrl+Space。

Seems to work in Excel, Google Sheets, OpenOffice Calc, and Gnumeric (all the programs I tried it in). (Thanks to https://productforums.google.com/forum/#!topic/docs/gJh1rLU9IRAfor pointing this out)

似乎适用于 Excel、Google Sheets、OpenOffice Calc 和 Gnumeric（我尝试过的所有程序）。（感谢https://productforums.google.com/forum/#!topic/docs/gJh1rLU9IRA指出这一点）

Unfortunately, not as nice as the formula and macro-based solutions (which worked for me BTW), because the highlighting goes away upon moving the cursor, but it also doesn't require the hassle of setting it up each time, or making a template with it (which I couldn't get to work).

不幸的是，不如公式和基于宏的解决方案好（顺便说一句，它对我有用），因为在移动光标时突出显示消失了，但它也不需要每次都设置它的麻烦，或者制作一个模板（我无法开始工作）。

Also, I found you could simplify the conditional formatting formula (for Excel) from the other solutions into a single formula for a single rule as:

此外，我发现您可以将其他解决方案中的条件格式公式（用于 Excel）简化为单个规则的单个公式，如下所示：

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

Trade off being that, if you did it this way, the highlighted column and row would have to use the same formatting, but that's probably more than adequate for most cases, and is less work. (thanks to https://trumpexcel.com/highlight-active-row-column-excel/for abbreviated formula)

权衡一下，如果你这样做，突出显示的列和行将必须使用相同的格式，但这对于大多数情况来说可能已经足够了，而且工作量更少。（感谢https://trumpexcel.com/highlight-active-row-column-excel/的缩写公式）

回答by Nybbe

First of all Thanks! I had just created a solution with highlighting cells, using the Selection_Change and changing a cells content. I did not know it would disable Undo. I found a way to do it by using combining conditional formatting, Cell() and the Selection_Change event. This is how I did it.

首先谢谢！我刚刚创建了一个突出显示单元格的解决方案，使用 Selection_Change 并更改单元格内容。我不知道它会禁用撤消。我找到了一种方法，通过结合使用条件格式、Cell() 和 Selection_Change 事件。我就是这样做的。

• In Cell A1 I put the formula =Cell("row")
• Row 2 is completely empty
• Row 3 contains the headers
• Row 4 and down is the data
• To make the formula in A1 to be updated, the sheet need to recalculate. I can do that with F9, but I created the Selection_Change eventwith the only code to be executed is Range("A1").Calculate. This way it is done every time the user moves around, and as the Selection_Change is NOT changing any values/formats etc in the sheet, Undo is not disabled.
• Now just enter the conditional formattingto highlight the cells that have the same row as cell A1.
  • Select the whole column B
  • Conditional Formatting, Manage Rules, New Rule, Use a Formula to determine which cells to format
  • Enter this formula: =Row(B1)=$A$1
  • Click Format and select how you want it to be highlighted
  • Ready. Press OK in the popups.

• 在单元格 A1 我把公式=Cell("row")
• 第 2 行完全为空
• 第 3 行包含标题
• 第 4 行及以下是数据
• 要更新 A1 中的公式，需要重新计算工作表。我可以用 F9 做到这一点，但我创建了Selection_Change 事件，唯一要执行的代码是Range("A1").Calculate. 这样，每次用户移动时都会执行此操作，并且由于 Selection_Change 不会更改工作表中的任何值/格式等，因此不会禁用撤消。
• 现在只需输入条件格式以突出显示与单元格 A1 具有相同行的单元格。
  • 选择整个B列
  • 条件格式、管理规则、新规则、使用公式确定要设置格式的单元格
  • 输入这个公式：=Row(B1)=$A$1
  • 单击“格式”并选择要突出显示的方式
  • 准备好。在弹出窗口中按确定。

This works for me.

这对我有用。

回答by Aato

An alternative to Range.Calculateis using ActiveWindow.SmallScrollThe only downside is that the screen flickers for a split second after making a new selection. While scrolling manually, you need to make sure the new selection moves out of the screen (window) completely, for it to work. Which is why, in below code, we need to scroll enough to get all visible rows out of the screen view and then scroll back to same position -to force screen refresh for conditional formatting.

一种替代Range.Calculate是使用ActiveWindow.SmallScroll唯一的缺点是，屏幕闪烁用于制造新的选择后一秒钟。手动滚动时，您需要确保新选择完全移出屏幕（窗口），以使其正常工作。这就是为什么，在下面的代码中，我们需要滚动到足以让所有可见行离开屏幕视图，然后滚动回相同位置 - 强制屏幕刷新以进行条件格式设置。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ScreenUpdating = False
ActiveWindow.SmallScroll Down:=150 'change these values to total rows displayed on screen
ActiveWindow.SmallScroll Down:=-150 'change these values to total rows displayed on screen
'DoEvents 'unable to use this to remove the screen flicker
ScreenUpdating = True
End Sub

Credits: Rory Archibald https://www.experts-exchange.com/questions/28275889/When-is-excel-conditional-formatting-refreshed.html

学分：Rory Archibald https://www.experts-exchange.com/questions/28275889/When-is-excel-conditional-formatting-refreshed.html

回答by Felipe Ribeiro

Using conditional formatting, instead of highlighting the entire row and column, it is possible to highlight the row to the left of the cell and the column above the cell with the code below:

使用条件格式，而不是突出显示整个行和列，可以使用以下代码突出显示单元格左侧的行和单元格上方的列：

=OR(AND(CELL("col")=COLUMN();(CELL("row")-1)>=ROW());AND(CELL("col")>=COLUMN();(CELL("row")-1)=ROW()))

回答by Colin Nunn

On the sheets Selection_change event call the following:

在工作表 Selection_change 事件上调用以下内容：

Function highlight_Row(rngTarget As Range)
    Dim strRangeRow As String
    strRangeRow = rngTarget.Row
    strRangeRow = strRangeRow & ":" & strRangeRow
    Rows(strRangeRow).Select
    rngTarget.Activate
End Function

This is long format for clarity!

为了清楚起见，这是长格式！