Hi,

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

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

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

Can anybody help me with this?

Thanks in advance, Titus

--
titushank
-----------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...fo&userid=1999
View this thread: http://www.excelforum.com/showthread.php?threadid=46919

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

Try the following code:

Code:
--------------------
Private Sub CommandButton1_Click()
For Each cell In Sheets(1).Cells 'or Range("A1:G12")'
If cell.HasFormula Then cell.Interior.ColorIndex = 5 'or cell.Font.ColorIndex = 5
Next cell
End Sub
--------------------


That'll take some time, so you probably should define a specific Range
instead of working with every cell in the active sheet.

the .interior version changes BackgroundColor
the .font version changes TextColor

hope it'll help:),

Simon


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

Hi Bernie,
thanks for your quick reply!

Actually, the "Sub ColorNoFormulasAllCells()" is exactly what I was
looking for and works great!!!

How can I get Excel to apply this automatically so that I don't have to
"run" the Macro every time? Ideally, anytime somebody overwrites a
formula with a manual input, the cell should be highlighted
immediately. How can I accomplish this?

NB: I know that I could protect all the cells from manual input, but
for the stuff I am working on I WANT people to overwrite formulas with
their own values, just that I want to visualize it..

Thanks for all your help!!!
Highly appreciate it!


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

Titus,

Copy the code below, right-click on the worksheet tab, select "View Code", and paste the code in the
window that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
ColorNoFormulasAllCells
End Sub


"titushanke" wrote in message
...

Hi Bernie,
thanks for your quick reply!

Actually, the "Sub ColorNoFormulasAllCells()" is exactly what I was
looking for and works great!!!

How can I get Excel to apply this automatically so that I don't have to
"run" the Macro every time? Ideally, anytime somebody overwrites a
formula with a manual input, the cell should be highlighted
immediately. How can I accomplish this?

NB: I know that I could protect all the cells from manual input, but
for the stuff I am working on I WANT people to overwrite formulas with
their own values, just that I want to visualize it..

Thanks for all your help!!!
Highly appreciate it!


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

Hi Bernie,

thank you so much, you are awesome!!! You can't imainge how muc
headache this stupid conditional formatting had given me!

By the way, not that I want to press, but do you think you would have
"magical" solution to my other problem too?

http://www.excelforum.com/showthread.php?t=467476

Thanks for looking into it, should you have a moment. Hope to be abl
to provide some responses to this forum too, soon, not just ask dum
questions :-)

Thanks again and greetings from Florence, Italy,

Titus

--
titushank
-----------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...fo&userid=1999
View this thread: http://www.excelforum.com/showthread.php?threadid=46919

Titus,

Other problem?

Something like

=ROUND(Number of Rooms/Days in reference month*Days in new year,0)

So for Tuedays, you would have

=ROUND(22/4*5,0)

etc...

HTH,
Bernie
MS Excel MVP

By the way, not that I want to press, but do you think you would have a
"magical" solution to my other problem too?

http://www.excelforum.com/showthread.php?t=467476

Thanks for looking into it, should you have a moment. Hope to be able
to provide some responses to this forum too, soon, not just ask dumb
questions :-)

Thanks again and greetings from Florence, Italy,

Titus.


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

Dear all,

I am using the macro as defined earlier:


Code:
--------------------
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
--------------------


The problem I have is that when I do an operation in many cells (like
"search and replace"), the screen blinks millions of times as the macro
is working through all cells for every replace check...

Is there any solution to avoid this as this is taking loads of time? I
remember there was some operation to have macros run without screen
refresh, but I couldn't find it again here in the forum....

Thanks to all reading this,

Titus.


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

Dear all,

o.k., I found the screenupdating function and included it.
Unfortunately this does not the resolve the issue, although it doe
speed up the macro a little bit.

Furthermore I noticed, that when the macro is running, I cannot UND
any cell operations any more. This is a great disadvantage toward
conditional formatting of course, so I would be very grateful for an
constructive ideas...

Thanks to the community!!!

Titus

--
titushank
-----------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...fo&userid=1999
View this thread: http://www.excelforum.com/showthread.php?threadid=46919

Hi Titus,
Try turning off calculation and screen refresh:
http://www.mvps.org/dmcritchie/excel...htm#slowmacros
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"titushanke" wrote in message
...

Dear all,

I am using the macro as defined earlier:


Code:
--------------------
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
--------------------


The problem I have is that when I do an operation in many cells (like
"search and replace"), the screen blinks millions of times as the macro
is working through all cells for every replace check...

Is there any solution to avoid this as this is taking loads of time? I
remember there was some operation to have macros run without screen
refresh, but I couldn't find it again here in the forum....

Thanks to all reading this,

Titus.


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

Loss of UNDO is a problem with all macros and you would have
even more of a problem if you chose to use an Event macro because it
is always there.

Dear David,

thanks for the explanation. I actually AM using an event macro an
therefore everytime I modify a cell the macro runs and the change
become permanent.

I think I will take out the event macro and place a button on the shee
to run the macro AFTER all modifications have been made...

Community:
Otherwise.. is there any other possibility to do CONDITIONAL FORMATTIN
without the limitation of these 2050 something rows? I have a file tha
is 30 MB and I unfortunately cannot split the sheets into separat
files...

Thanks for any "out of the box" ideas!

Titus

--
titushank
-----------------------------------------------------------------------
titushanke's Profile: http://www.excelforum.com/member.php...fo&userid=1999
View this thread: http://www.excelforum.com/showthread.php?threadid=46919