Code to Highlight / Un-Highlight Cells

Salts

New Member
Joined
Aug 21, 2019
Messages
16
I have a large spreadsheet that will constantly vary in regards to number of rows.
Working on a macro that will highlight (fill) all blank cells in red, then after info is entered in cells it un-highlights (fills).
This is the macro I have started & it colors the cells, but after I enter info in blank cells & re-run macro those cells are still highlighted in red.
Appreciate any help.

Sub HighlightBlankCells()


Dim Dataset As Range
Set Dataset = Selection
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
Code:
[color=darkblue]Sub[/color] foo()
    [color=darkblue]With[/color] Selection
        .Interior.ColorIndex = xlNone
        .SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

Salts

New Member
Joined
Aug 21, 2019
Messages
16
Thanks AlphaFrog, that seems to work, except it then took out the highlight in my header row 1 as well.
Is there a way to keep this from removing the color in row 1.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
This will use highlight blanks in your used range excluding Row 1. You don't have to Select anything.

Code:
[color=darkblue]Sub[/color] foo()
    [color=darkblue]With[/color] ActiveSheet.UsedRange
        [color=darkblue]With[/color] .Offset(1).Resize(.Rows.Count - 1)
            .Interior.ColorIndex = xlNone
            .SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
Your welcome.

FYI: you could do this with a Table and Conditional Formatting.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,827
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top