如何使用 VBA 宏突出显示空/空白单元格

How to highlight empty/blank cells using VBA macro

提问by user3688713

I realized I messed up asking my very first question, so I will try one last time. I am targeting the same 4 columns from 2 separate sheets that have cells that either contain text or do not. Sheet 1 will be updated automatically, so I will be running this code daily to manually update sheet 2. I am trying to find a way to basically find out which cells are missing the text using a macro. I tried using a code that I found on this website that puts borders on cells containing text and clears borders for empty cells.

我意识到我在问我的第一个问题时搞砸了，所以我会最后一次尝试。我的目标是来自 2 个单独工作表的相同 4 列，这些工作表的单元格包含文本或不包含文本。工作表 1 将自动更新，因此我将每天运行此代码以手动更新工作表 2。我试图找到一种方法来基本上找出使用宏缺少文本的单元格。我尝试使用我在本网站上找到的代码，该代码在包含文本的单元格上放置边框并清除空单元格的边框。

Sub BorderForNonEmpty()
Dim myRange As Range
Set myRange = Sheet1.Range("C2:C252")

' Clear Existing Borders
myRange.Borders.Linestyle = xlLineStyleNone

' Test Each Cell and Put a Border Around it if it has content
For Each myCell in myRange
If myCell.Text <> "" Then
myCell.BorderAround (xlContinuous)
End If
Next
End Sub

This code works, but I want to try to highlight the empty cells with a color opposed to clearing its border. This is also my first time posting on StackOverflow, so I apologize beforehand. Thank you.

此代码有效，但我想尝试使用与清除边框相反的颜色突出显示空单元格。这也是我第一次在 StackOverflow 上发帖，所以我事先道歉。谢谢你。

回答by guitarthrower

Instead of looping through all cells, Excel has a built in function to select blank Cells. This should be faster, and more reliable.

Excel 有一个内置函数来选择空白单元格，而不是遍历所有单元格。这应该更快，更可靠。

Sub BorderForNonEmpty()
    Dim myRange As Range
    Set myRange = Sheet1.Range("C2:C252")

    'clear all color
    myRange.Interior.ColorIndex = xlNone

    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
End Sub

Another option could be to just use conditional formatting (another built-in feature), but that can be hard to control for changing ranges.

另一种选择可能是仅使用条件格式（另一个内置功能），但这可能很难控制更改范围。

回答by avb

Replace

代替

myCell.BorderAround (xlContinuous)

with

和

myCell.Interior.Color = RGB(100, 100, 100)

回答by Gary's Student

Give this a try:

试试这个：

Sub BorderForNonEmpty()
    Dim myRange As Range
    Set myRange = Sheet1.Range("C2:C252")
    For Each myCell In myRange
        If myCell.Text = "" Then
            myCell.Interior.ColorIndex = 6
        End If
    Next
End Sub

EDIT#1:

编辑#1：

Sub BorderForNonEmpty()
    Dim myRange As Range
    Set myRange = Sheet1.Range("C2:C252")
    For Each myCell In myRange
        If myCell.Text = "" Then
            myCell.Interior.ColorIndex = 6
        Else
            myCell.Interior.ColorIndex = xlNone
        End If
    Next
End Sub

EDIT#2:

编辑#2：

To make the macro "clickable":

要使宏“可点击”：

1. Put any AutoShape on the worksheet
2. Format the AutoShape
3. Right-click the AutoShape and assign the macro to it.

1. 将任何自选图形放在工作表上
2. 格式化自选图形
3. 右键单击自选图形并将宏分配给它。