identifying blank cells by a colour

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet with data and some areas do not have data to be filled in
later. Before checking I want to identify the cells which are blank with a
color so that these can be looked and data fed. i think this can be done by
go to special command. But I want some one to provide me with a code so that
the same can be used for other worksheets also.

thank in advance. I am using excel 2003

seenu
 
Hi Srinivasan,

Try:

'=============>>
Public Sub Tester()
Dim rng As Range

Set rng = Range("A1:A50") '<<==== CHANGE

On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
On Error GoTo 0

End Sub
'<<=============
 
Enter this macro:

Sub Macro1()
Dim r As Range
For Each r In Selection
If IsEmpty(r.Value) Then
r.Interior.ColorIndex = 6
End If
Next
End Sub

Select any range of cells that you want to examine and mark and run the macro.

Please note that this macro will only color empty cells, not cells that are
blank by reason of formula result.

Have a good day!
 
Hi,

Try this:

Selection.SpecialCells(xlCellTypeBlanks).Select

of course, you may change Selection to a specifice range, or a variable that
references a range

HTH

Philip
 
While working with Edit>Go To>Special>Blanks, it captures Merged Cells as
blanks and deletes the values inside. Is there a way, we can control
operation on Merged Cells.
 
SpecialCells on its own does not delete anything. What's the overall
objective.

Regards,
Peter T
 
Back
Top