Titus,

You didn't say what you wanted to do with blanks: see the last of the examples below that shows how
to handle blanks.

HTH,
Bernie
MS Excel MVP

Sub ColorNoFormulasAllCells()
Cells.Interior.ColorIndex = xlNone 'Optional, to reset shading
On Error GoTo AllFormulas
With Cells.SpecialCells(xlCellTypeConstants, 23).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
AllFormulas:
End Sub

Sub ColorNoFormulasSpecificCells()
Cells.Interior.ColorIndex = xlNone 'Optional, to reset shading
On Error GoTo AllFormulas
With RAnge("A1:A100").SpecialCells(xlCellTypeConstants, 23).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
AllFormulas:
End Sub

Sub ColorConstantsAndBlanks()

Cells.Interior.ColorIndex = xlNone 'Optional, to reset shading
On Error GoTo NoConstants
With Cells.SpecialCells(xlCellTypeConstants, 23).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
NoConstants:
Resume Next
On Error GoTo NoBlanks
With Cells.SpecialCells(xlCellTypeBlanks).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
NoBlanks:
End Sub


"titushanke" wrote in message
...

Hi,

I have a file that contains more than 2050 rows and therefore I cannot
use conditional formatting (limitation).

What I am looking for is some VBA code that highlights a cell (pattern
color) if there is NO FORMULA in this cell.

This could be valid for a whole sheet, no need to specify a range (but
it would be cool to know both ways).

Can anybody help me with this?

Thanks in advance, Titus.


--
titushanke
------------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...o&userid=19997
View this thread: http://www.excelforum.com/showthread...hreadid=469193