Conditional Formatting type of operation

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.

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" <titushanke.1vo0ee_1127232348.9571@excelforum-nospam.com> wrote in message
news:titushanke.1vo0ee_1127232348.9571@excelforum-nospam.com...
>
> 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:

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

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!

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" <titushanke.1vpbmc_1127293529.2203@excelforum-nospam.com> wrote in message
news:titushanke.1vpbmc_1127293529.2203@excelforum-nospam.com...
>
> 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 much headache this stupid conditional formatting had given me!

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.

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:

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.

Dear all,

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

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

Thanks to the community!!!

Titus.

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 and therefore everytime I modify a cell the macro runs and the changes become permanent.

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

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

Thanks for any "out of the box" ideas!

Titus.

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" <titushanke.1wsr2g_1129133134.1155@excelforum-nospam.com> wrote in message
news:titushanke.1wsr2g_1129133134.1155@excelforum-nospam.com...
>
> 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
>