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

2021年9月29日 18点热度 0条评论 来源: wotent

本文原创,转载请注明出处:https://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 高亮显示选择行列【不影响格式】https://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的名称)并点击确定,这样,就不会每次都出现“启用宏”的提示了。

    原文作者:wotent
    原文地址: https://www.cnblogs.com/wotent/p/15348891.html
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。