Note: The other languages of the website are Google-translated. Back to English
登陆  \/ 
x
or
x
点此注册  \/ 
x

or

在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应用程序 窗口。

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


单击以突出显示Excel中所选单元格的行和列:

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天退款保证。
kte选项卡201905

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

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
Say something here...
symbols left.
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 · 10 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
  • To post as a guest, your comment is unpublished.
    Kris · 1 years ago
    @shea honig 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.
  • To post as a guest, your comment is unpublished.
    kiba fil · 1 years ago
    Thank you. Great Job
  • 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
  • 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
  • To post as a guest, your comment is unpublished.
    crystal · 1 years ago
    @Vishnu Subind 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
  • 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
  • To post as a guest, your comment is unpublished.
    rahayu · 2 years ago
    trimakasih
  • To post as a guest, your comment is unpublished.
    rahayu · 2 years ago
    td muncul=tidak muncul
  • To post as a guest, your comment is unpublished.
    rahayu · 2 years ago
    Salam...
    sy sudah install kutools, tp kok td muncul tab kutools di layar excel ya? gmn cara mmunculkannya?trimakasih
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @vuongngocsangbkdn@gmail.com Good day,
    Sorry can't help you with that. Thanks for commenting.
  • 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?
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @devika.kalika@gmail.com 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?
  • 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.
  • 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?
  • 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 :)
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Michelle 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.
  • 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!
  • 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.