跳到主要內容

在Excel中單擊或選擇單元格時如何更改單元格顏色?

本文將向您展示一些方法,這些方法如何在單擊單元格時更改單元格顏色,並在Excel中選擇一系列單元格時更改突出顯示的顏色。

使用VBA代碼單擊單元格時更改單元格顏色
使用VBA代碼選擇單元格時更改突出顯示的顏色
用Kutools for Excel突出顯示活動單元格的整個行和列


使用VBA代碼單擊單元格時更改單元格顏色

在這裡,您可以使用以下VBA代碼雙擊或右鍵單擊單元格來更改其背景顏色。

1.在工作表中,單擊時將更改單元格顏色,右鍵單擊工作表選項卡,然後單擊 查看代碼 從右鍵單擊菜單中。

2。 在裡面 Microsoft Visual Basic for Applications 窗口,請複制以下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 for Applications 窗口。

然後,當您雙擊一個單元格時,它將被塗成紅色。 當您右鍵單擊一個單元格時,該單元格將變為綠色。 看截圖:


單擊以突出顯示Excel中所選單元格的行和列:

Excel的Kutools's 閱讀版式 實用程序可幫助您快速突出顯示Excel中所選單元格的行和列,如下面的演示所示。
立即下載並試用! (30 天免費試用)


使用VBA代碼選擇單元格時更改突出顯示的顏色

默認情況下,選擇單元格範圍時,突出顯示的顏色為灰色。 如果要使所選範圍更加突出,可以將其突出顯示的顏色更改為所需的顏色。 請執行以下操作。

1.右鍵單擊要更改所選範圍的突出顯示顏色的圖紙選項卡,然後單擊 查看代碼 從右鍵單擊菜單中。

2。 在裡面 Microsoft Visual Basic for Applications 窗口,請複制以下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 for Applications 窗口。

從現在開始,所選範圍或單元格的突出顯示顏色將變為黃色。 當您單擊另一個單元格或單元格範圍時,單元格將自動著色。


用Kutools for Excel突出顯示活動單元格的整個行和列

如果要處理較大的工作表,則必須突出顯示活動單元格的整個行和列,以便更輕鬆地閱讀。 在這裡 閱讀版式 的效用 Excel的Kutools 可以幫你。

申請前 Excel的Kutools首先下載並安裝.

1。 點擊 庫工具 > 閱讀版式。 看截圖:

然後啟用閱讀佈局,您可以看到活動單元格的行和列立即突出顯示。

備註:您可以根據需要更改閱讀佈局設置,如下圖所示。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


用Kutools for Excel突出顯示活動單元格的整個行和列

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (28)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, i built a dashboard with 37 worksheets, everyone with a table. All of them are lock panels because are to large and i need to know the number and name of the student who am i entering data for. Despite that, i have to keep my finger on the left side of the screen to identify the names and numbers to which the entered data refer.  So, i need to identify the first two cells of the row, in the columns "I" and "J".
The code that i took from the internet and couldn't change for the two cells of columns "I" and "J" was:

Dim lTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row >= 16 Then

If Not lTarget Is Nothing Then

lTarget.EntireRow.Interior.ColorIndex = 0
End If

Target.EntireRow.Interior.Color = 9359529

Set lTarget = Target
End If
End Sub

All tables begin in diferent rows. This example start in 16 row but i put the code in all the pages and adapt to the needs of the specific row number. 
I've already tried to change the code from EntireRow to Range ("I16:J43") but the cells were in the range became all painted with the code color.
Already try a diferent way through the "conditional formatting" with the formula =E($I16<>"";LIN()=CÉL("lin")) in the range $I16:$J43, and in the developer "worksheet", with "Application.Calculate" and it works. I change in the 37 worksheets but i was copying and data from the previous file to this new one and I noticed that the options to paste "values", etc, disappear from my dashboard. The code must be executing something that, like clicking on the cell, no longer lets it paste. A single option was to paste with CTRL+V.
If someone can help me with the code, i'll delete the VBA "Application.Calculate" to to get back to the available collage options.
Thak you for your help.

Luís Lopes
This comment was minimized by the moderator on the site
Hello, the code used to work for a while, but now it doesn't anymore. What can there be wrong?
This comment was minimized by the moderator on the site
Hi Rens Borburgh,There are two codes provided in the post, which one did you use? Did you get any error prompt?
This comment was minimized by the moderator on the site
I have tried them both at the same time. After copying it to a usb stick it didn't work anymore.
This comment was minimized by the moderator on the site
Hi Rens Borburgh,I don't quite understand what you mean. The codes should be used in the sheet code window and not in the Module window. Or can you provide a screenshot of your code window?
This comment was minimized by the moderator on the site
Hi, I am trying to use the double click option to change a cell to green but i also want that cell to go back to no fill or white if i double click it whilst it is green. Is this possible? 
This comment was minimized by the moderator on the site
alguien que me ayude solo quiero que al hacerle click a una celda se ponga de un color y que se mantenga ese color y si le vuelvo hacer click regrese el color original que tenia
This comment was minimized by the moderator on the site
How to highlight the cell(with data) to automatically highlight the other cells (data which is related another data in a previous cell) with click of the cell.Can i do it. Is there a way to do it.
This comment was minimized by the moderator on the site
The code works as long as I do not protect the worksheet. Once I protect the worksheet, because I have conditional formatting on locked cells, the code will not work. How can I change this so that it works on locked worksheet where the user can select unlocked cells?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Thank you. Great Job
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations