CODEWITHSUNDEEP
vbaexcel
user3688713
user3688713

Reputation: 41

How to highlight empty/blank cells using VBA macro

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. 

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.

Upvotes: 2

Views: 38356

Answers (3)

guitarthrower
guitarthrower

Reputation: 5834

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

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.

Upvotes: 4

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

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:

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:

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.

Upvotes: 0

avb
avb

Reputation: 1753

Replace 

myCell.BorderAround (xlContinuous)

with 

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

Upvotes: 0

Related Questions