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

 

[evandec]

Rorya thank you for the patient response. However, there are times when I would prefer to have "no fill as" for each new workbook. This is mostly for when a sheet is almost done and I want to clean it up.

 

[Greg Truby]

Hmmm, and here I thought for sure that Rory would have some fabulous esoteric explanation about why the blank cells worked that way...

In a lot of the spreadsheets I put together I end up coloring the background white. For me it is easier to read...

The implication is that the background color does not show as white for empty cells. You could always just change your windows system colors, you know.

This is mostly for when a sheet is almost done and I want to clean it up.

In which case the blankcells against the UsedRange would get most of what you want.

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 had the time, I'm sure we could work out something that would detect the screen size and do a smart job of estimating the number of additional columns and rows needed to fill. But given the ROI, you might just go dumdum school and count the number of columns and rows visible on your screen at standard widths & heights and just paint that many right & down from the UsedRange.

 

[RoryA]

Rorya thank you for the patient response. However, there are times when I would prefer to have "no fill as" for each new workbook. This is mostly for when a sheet is almost done and I want to clean it up.

I'm afraid I have no idea how this relates to my suggestion?

 

[RoryA]

Hmmm, and here I thought for sure that Rory would have some fabulous esoteric explanation about why the blank cells worked that way...

if they are not in the Usedrange, how could they possibly be special??

I'm glad it works this way - much easier to select a whole column and choose edit-goto, special... blanks and manipulate them without having to worry about extending the used range to the bottom of the sheet!