Excel 高亮当前行、高亮重复行的探索

  • 2021 年 9 月 28 日
  • 笔记

本文原创，转载请注明出处：//www.cnblogs.com/wotent/p/15348891.html

TLDR

下载文件 高亮.zip ，将解压后的“高亮.xlam”保存在 C:\Users\用户名\AppData\Roaming\Microsoft\AddIns 中。

按照本文最后的步骤5和步骤6设置即可。

介绍

在 Excel 的日常操作中，经常需要用到高亮当前行或当前列的功能，如 WPS 的“阅读模式”和易用宝的“聚光灯”功能。

但无论 WPS 还是 易用宝，都缺少我自己需要的另外一个功能：高亮重复值。

我需要的“高亮重复值”功能，并不是条件格式中的“重复值”，而是高亮显示与活动单元格具有相同值的单元格。

 

 第一阶：高亮当前行（条件格式）

此方法用到的公式：

=ROW()=CELL("row")

此方法网络上有很多教程，简述如下：

  1. 选择全部单元格
  2. “开始” => “条件格式” => “管理规则”
  3. 新建规则
  4. 使用公式确定要设置格式的单元格
  5. 填入上述公式
  6. 点击“格式按钮” => “填充”选项卡，选择喜欢的高亮当前行的颜色
  7. 一路确定

这便用条件格式实现了高亮当前行的功能。

但是，此时的高亮不会随着活动单元格的改变自动刷新，按 F9（重新计算） 可手动刷新高亮行的位置。

第二阶：添加高亮重复值的功能

此方法用到的公式：

=IF(CELL("type") = "b",FALSE,CELL("contents")=CELL("contents", INDIRECT(ADDRESS(ROW(), COLUMN()))))

受高亮当前行方式的启发，找到了高亮重复值的方法：

再添加一项条件格式，填入上述公式，将两条规则的关系按下图设置，并注意两点：

  1. 高亮重复值的规则在上，高亮当前行的规则在下
  2. “如果为真则停止”全部勾选

第三阶：VBA 实现自动刷新

此方法从网络获得，具体链接现在搜索不到了。

  1. 右键单击底部的工作表名称，选择“查看代码”命令
  2. 在代码框输入以下代码并退出 VBA 编辑器
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

这便实现了高亮行的自动刷新，高亮行始终显示在活动单元格的那一行上，不用再手动点击 F9 了。

但是，添加了 VBA 代码后，不能保存为 xlsx，需要保存为 xlsm。

我按照上述的方法制作了一个空的 excel 文件，在此后的相当长一段时间内，我都复制这个“模板”来处理 Excel 文档。

第四阶：

第三阶的方法，使用了很长时间，但有两个很致使的问题：一是将文件发给他人的时候，会带着 VBA 代码和条件格式，以 xlsm 的格式发出去，造成他人的不便；二是处理普通 Excel 文件时，不能很方便的应用高亮功能（需要手动添加条件格式和 VBA 代码）。

前几天看到一篇文章（Excel 高亮显示选择行列【不影响格式】//club.excelhome.net/thread-1372036-1-1.html），受其中注册事件用法的启发，决定将 VBA 代码做成共享的，使 Excel 启动时自动加载，并用 VBA 实现添加条件格式，这样，对于任何 Excel 文件，只需点几下按钮即可实现高亮功能，且保存后的文件不带有任何 VBA 代码。

步骤1：

打开 Excel 程序，直接另存为 高亮.xlam 文件，保存至 C:\Users\用户名\AppData\Roaming\Microsoft\AddIns 文件夹中。

步骤2：

打开此 高亮.xlam 文件，点击“开发工具”选项卡中的“Visual Basic”打开“Microsoft Visual Basic for Applications”。（没有“开发工具”选项卡的自行搜索添加“开发工具”选项卡的方法）

步骤3：

菜单“插入” => “模块”

在对应的代码窗口输入以下代码：

 1 Option Explicit
 2 Public HL
 3 Sub 自动刷新打开()
 4 Set HL = New SheetSelectionChangeHandler
 5 Set HL.handler = Application
 6 End Sub
 7 
 8 Sub 自动刷新关闭()
 9 HL = Null
10 End Sub
11 Sub 条件格式高亮添加()
12     Dim selectedRange As Range
13     Set selectedRange = Selection
14     
15     Cells.Select
16     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ROW()=CELL(""row"")"
17     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
18     With Selection.FormatConditions(1).Interior
19         .Color = RGB(146, 205, 220)
20     End With
21     Selection.FormatConditions(1).StopIfTrue = True
22     
23     Cells.Select
24     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(CELL(""type"") = ""b"",FALSE,CELL(""contents"")=CELL(""contents"", INDIRECT(ADDRESS(ROW(), COLUMN()))))"
25     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
26     With Selection.FormatConditions(1).Interior
27         .Color = RGB(0, 176, 90)
28     End With
29     Selection.FormatConditions(1).StopIfTrue = True
30     selectedRange.Select
31 End Sub

步骤4：

菜单“插入” => “类模块”

在属性窗口（如没有，按 F4 打开属性窗口）中将类模块名称改为SheetSelectionChangeHandler 

在对应的代码窗口中输入以下代码：

1 Option Explicit
2 
3 Public WithEvents handler As Application
4 
5 Private Sub handler_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
6 Application.ScreenUpdating = True
7 End Sub

一定要在“Microsoft Visual Basic for Applications”窗口中点击保存按钮，如果直接关掉该窗口，默认是不保存的。

步骤5：

打开“文件” => “选项” => “自定义功能区”，“从下列位置选择命令”设置为“宏”，设置为如下图所示结构：

大功造成，现在，可以在“我的工具”选项卡中，点击“条件格式高亮添加”按钮来添加高亮相关的条件格式，点击“自动刷新打开按钮”使高亮区域根据活动单元格自动刷新。

步骤6：

点击“开发工具”选项卡的“Excel 加载项”命令，选中“高亮”（高亮.xlam的名称）并点击确定，这样，就不会每次都出现“启用宏”的提示了。

VirMach 便宜 VPS

QNews

QNews