Code to Highlight / Un-Highlight Cells

[Salts]

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

 

[AlphaFrog]

[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]

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]

This will use highlight blanks in your used range excluding Row 1. You don't have to Select anything.

[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]

 

[Salts]

AlphaFrog - that works, your Awesome! Thanks.

 

[AlphaFrog]

Your welcome.

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