Problem with .specialcells(xlCellTypeBlanks)

[evandec]

I want to select all blank cells in on a worksheet and color then white. I tried using the code included below on a new blank worksheet but it returned an error saying that no cells were returned. Am I misunderstanding how .SpecialCells(xlCellTypeBlanks) works?

Dim colRng As Range

    ActiveSheet.Cells.Select
    Set colRng = Selection.SpecialCells(xlCellTypeBlanks)
    

    With colRng.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
    End With


End Sub

 

[mvptomlinson]

Any reason why you want to select the entire sheet? I'd recommend either setting a static range, or using the last used column and last used row in the macro to set some boundaries.

The code below should do what you're asking, but only over range A1:N13. Expand as needed, or if you need help using variables to capture the last row or col let us know.

Sub myMacro()
With ActiveSheet.Range("A1:N13").SpecialCells(xlCellTypeBlanks)
  .Interior.ColorIndex = 2
  .Interior.Pattern = xlSolid
End With
End Sub

 

[Mike Blackman]

Hi,

Maybe;

    With ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Interior
        .ColorIndex = 2
        .Pattern = xlSolid
    End With

 

[Greg Truby]

The answer to your question, BTW, is that [for reasons I do not know], the specialcells(xlcelltypeblanks) only returns the blank cells in the usedrange. If you select empty cells to the right and/or below the last non-blank cell in your worksheet, and try to go to blank cells using the GOTO | SPECIAL menu options, you will also be told that no blank cells are to be found.

Since you are trying this on a new worksheet, it errors out. So, no, you didn't misunderstand the concept. You just didn't know about that particular quirk of it.

 

[evandec]

Greg very interesting.

Is there any way around this to accomplish my goal? The only thing I can really think of is to the bottom right hand corner of the worksheet, enter some data and then run the macro. This seems cumbersome since after doing that you will get errors when you insert columns or rows because excel thinks you are trying to push data off the valid range of the worksheet.

 

[mvptomlinson]

Again, what is the purpose of coloring 16,777,216 cells white? I know asking a question doesn't solve your dilemma, but I can't imagine anyone using that many cells on one worksheet and being productive.

 

[evandec]

In a lot of the spreadsheets I put together I end up coloring the background white. For me it is easier to read. The visible screen will often extend past the cells that I am working with. Consequently, I would like to color those cells white too.

If I get handed a spreadsheet that already has a lot of formatting I cant just select all and color everything white and it is time consuming to manually go through all of the separate ranges and color them individually.

 

[RoryA]

You could just use:
Format-Style, select Normal, Modify, change the pattern to white and click Add.

 

[evandec]

Yes, but I would prefer not to do anything manually. I would like to run the macro and have it select all cells which are blanks and color them white.

 

[RoryA]

Just automate that then:

activeworkbook.Styles("Normal").Interior.ColorIndex = 2