highlight blank cells - vba

[lezawang]

Hi the code below (not mine thanks for your help) is to highlight blank cells (no cells has spacebar). It works perfectly but is the Selection has no empty cells or cells with spacebar then i get run-time "no cells we found" and the program terminate. Can that code modified so if the Selection has no empty cells then a message display (msgbox no empty cells found).
Thank you so much

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

 

[Fluff]

How about

Sub noblank()
On Error GoTo NoBlanks
Selection.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
On Error GoTo 0
Exit Sub
NoBlanks:
MsgBox "No Blanks"
End Sub

 

[lezawang]

Thank you so much. It works perfectly. Just one more question I have. This code, if I do not select (highlight anything) and the sheet is empty one then this code will make range A1:G47 red?! why is that

 

[Fluff]

I get the "No blanks" message.
Save the file & try again, does it still fill the range?

 

[lezawang]

Thanks for your reply. I create a new sheet and it works fine when nothing in the sheet. The I did the following

filled the range A1:C25 with random number, using he following code
++++++++++++++
Sub newfill()
Dim i As Integer
Dim j As Integer
For j = 1 To 3
For i = 1 To 25
Workbooks(1).ActiveSheet.Cells(i, j).Value = WorksheetFunction.RandBetween(1, 1000)
Next i
Next j
End Sub
+++++++++++
Then I cleared all the sheet using this code

Sub clearcontents2()
Workbooks(1).ActiveSheet.Cells.clear
End Sub

++++++

then when I tried to use your code, it will highlight the range A1:C25 only.

Thank you

 

[Fluff]

If you've cleared the cell they are blank, which is why the code highlights them