在Excel中单击或选择单元格时如何更改单元格颜色？

本文将向您展示一些方法，这些方法如何在单击单元格时更改单元格颜色，并在Excel中选择一系列单元格时更改突出显示的颜色。

使用VBA代码单击单元格时更改单元格颜色
 使用VBA代码选择单元格时更改突出显示的颜色
 用Kutools for Excel突出显示活动单元格的整个行和列

使用VBA代码单击单元格时更改单元格颜色

在这里，您可以使用以下VBA代码双击或右键单击单元格来更改其背景颜色。

1.在工作表中，单击时将更改单元格颜色，右键单击工作表选项卡，然后单击 查看代码 从右键单击菜单中。

2。在里面 Microsoft Visual Basic应用程序 窗口，请复制以下VBA代码并将其粘贴到代码窗口。

VBA：单击时更改单元格颜色

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbRed
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbGreen
End Sub

3。按其他 + Q 同时关闭按键 Microsoft Visual Basic应用程序 窗口。

然后，当您双击一个单元格时，它将被涂成红色。当您右键单击一个单元格时，该单元格将变为绿色。看截图：

Kutools for Excel's 阅读版式 实用程序可帮助您快速突出显示Excel中所选单元格的行和列，如下面的演示所示。
立即下载并尝试！（30天免费试用）

使用VBA代码选择单元格时更改突出显示的颜色

默认情况下，选择单元格范围时，突出显示的颜色为灰色。如果要使所选范围更加突出，可以将其突出显示的颜色更改为所需的颜色。请执行以下操作。

1.右键单击要更改所选范围的突出显示颜色的图纸选项卡，然后单击 查看代码 从右键单击菜单中。

2。在里面 Microsoft Visual Basic应用程序 窗口，请复制以下VBA代码并将其粘贴到代码窗口。

VBA代码：更改所选范围的突出显示颜色

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , "TRUE"
        .FormatConditions(1).Interior.Color = vbYellow
    End With
End Sub

3。按其他 + Q 按键同时关闭 Microsoft Visual Basic应用程序 窗口。

从现在开始，所选范围或单元格的突出显示颜色将变为黄色。当您单击另一个单元格或单元格范围时，单元格将自动着色。

用Kutools for Excel突出显示活动单元格的整个行和列

如果要处理较大的工作表，则必须突出显示活动单元格的整个行和列，以使阅读更加容易。在这里 阅读版式 实用程序 Kutools for Excel 可以帮你。

申请前 Kutools for Excel请 首先下载并安装.

1。点击 Kutools > 阅读版式。看截图：

然后启用了阅读布局，您可以看到活动单元格的行和列立即突出显示。

备注：您可以根据需要更改阅读布局设置，如下图所示。

如果您想免费试用该工具（30天）， 请点击下载，然后按照上述步骤进行操作。

用Kutools for Excel突出显示活动单元格的整个行和列

最佳办公效率工具

Kutools for Excel解决了您的大多数问题，并使您的生产率提高了80％

重用: 快速插入 复杂的公式，图表 以及您以前使用过的任何东西；加密单元带密码创建邮件列表并发送电子邮件...
超级公式栏（轻松编辑多行文本和公式）；阅读版式（轻松读取和编辑大量单元格）；粘贴到过滤范围...
合并单元格/行/列不会丢失数据；拆分单元格内容；合并重复的行/列...防止细胞重复；比较范围...
选择重复或唯一行; 选择空白行（所有单元格都是空的）；超级查找和模糊查找在许多工作簿中；随机选择...
确切的副本多个单元格，无需更改公式参考；自动创建参考到多张纸；插入项目符号，复选框等...
提取文字，添加文本，按位置删除，删除空间; 创建和打印分页小计；在单元格内容和注释之间转换...
超级滤镜（将过滤方案保存并应用于其他工作表）；高级排序按月/周/日，频率及更多；特殊过滤器用粗体，斜体...
结合工作簿和工作表; 根据关键列合并表；将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
超过300种强大功能。支持Office / Excel 2007-2019和365。支持所有语言。在您的企业或组织中轻松部署。完整功能30天免费试用。 60天退款保证。

阅读更多免费下载... 采购...

Office选项卡为Office带来了选项卡式界面，使您的工作更加轻松

在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
每天将您的工作效率提高50％，并减少数百次鼠标单击！

阅读更多免费下载... 采购...

19 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Vera · 8 months ago
Hi,
is it possible to have a code for "change cell color when clicking on it" in the way that it will not change already formated cells with another color, please?
After applying your code all my formating (colours) is away. Thank you. Veronika
- Reply
To post as a guest, your comment is unpublished.
kiba fil · 1 years ago
Thank you. Great Job
- Reply
To post as a guest, your comment is unpublished.
shea honig · 1 years ago
hi
the is working grate but i want that the color should only be when the cell is selected and when i select something else, the first cell i selcted should not be colored
can you help me pls with this thanks
- Reply
- To post as a guest, your comment is unpublished.
  Kris · 11 months ago
  
  I had that exact same question! I hope someone will answer you! The reason I am looking for this is I have a spreadsheet I use daily and put in lots of information into single cells. It gets hard to see the values (visually, it runs into info in other cells), and would be a great feature if I could change the color of the cell being edited - only while it is being edited. The simplest solution I can think of is that the cell value would be set back to what it was prior to being clicked on - but I don't know how to do that.
  
  Reply
To post as a guest, your comment is unpublished.
perry · 1 years ago
Hi, I am trying to figure out how to change the color of a cell, based on a selection from a dropdown list. For example, HIGH=red, MEDIUM=yellow, LOW=green.

Any tips you can provide are greatly appreciated. Thank You
- Reply
To post as a guest, your comment is unpublished.
Vishnu Subind · 1 years ago
Hi guys, so i used to first code to change the cell colour to green/red on doubleclick/rightclick. I would like this code, however, to work just on a specific range of cells, in my case D9:P9. can someone help me on how to do that? Im new at VBA
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi,
  The below VBA code can do you a favor, please have a try. Thank you for your comment.
  
  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If (Not Application.Intersect(Target, Me.Range("D9:P9")) Is Nothing) Then
  Cancel = True
  Target.Interior.Color = vbRed
  End If
  End Sub
  Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  If (Not Application.Intersect(Target, Me.Range("D9:P9")) Is Nothing) Then
  Cancel = True
  Target.Interior.Color = vbGreen
  End If
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
rahayu · 1 years ago
trimakasih
- Reply
To post as a guest, your comment is unpublished.
rahayu · 1 years ago
td muncul=tidak muncul
- Reply
To post as a guest, your comment is unpublished.
rahayu · 1 years ago
Salam...
sy sudah install kutools, tp kok td muncul tab kutools di layar excel ya? gmn cara mmunculkannya?trimakasih
- Reply
To post as a guest, your comment is unpublished.
vuongngocsangbkdn@gmail.com · 2 years ago
I have a problem.
I want change color of one hyperlink if it had linked with one PDF to RED and if had not link with one PDF will show BLUE color.
How can I do now?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Good day,
  Sorry can't help you with that. Thanks for commenting.
  
  Reply
To post as a guest, your comment is unpublished.
devika.kalika@gmail.com · 2 years ago
I tried the "Change the highlighted color when cells are selected with VBA code" option and saved my excel as Macro Enabled Worksheet. Now the last cells that were selected are highlighted and won't highlight other cells when selected. I've deleted code and reentered and saved, but still no change. I can't remove the previous highlight either. I'm at lost on what to do.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi,
  The code works well in my case after saving the workbook as an Excel Macro-Enabled Workbook.
  Can you tell me your Excel version?
  
  Reply
To post as a guest, your comment is unpublished.
Jason · 2 years ago
Is there a way to just apply this to certain cells while leaving other cells with available for number entering?
- Reply
To post as a guest, your comment is unpublished.
Slaxx · 2 years ago
hi. just a little heads up. i'm using office 2007.
i tried your code, and while it did work, it wasn't what i was looking for. so i just deleted the code completely. but the last selected/active cell before i deleted the code is still green (i changed the color from vbYellow to vbGreen), and the color won't go away. it's not affected by fill color either.
the simplest solution i found was to just delete the cell. copying and pasting will not work, it will also copy the color.
just thought i'd let you know. you might find another solution/troubleshooting for this, besides deleting and manual entry. still, thanks! i confirm that the code works :)
- Reply
To post as a guest, your comment is unpublished.
Michelle · 2 years ago
I love how easy formatting my worksheet the code for "change cell color when clicking on it" would make my daily work! I am hitting a snag though. When I copy my Blank Sheet with the code entered as described, I get a error. It runs great until I create new sheets. I am a novice at coding and am working from textbooks and the internet. I appreciate any help you will offer!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Dear Michelle,
  I followed the steps as you described, but the code also worked in my case. Can you tell me your Office version?
  Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
madhav neupane · 3 years ago
thank you thank you so much. I appreciate you for this.
- Reply

登录到您的帐户

重设密码