Show cells with formulas without permanently changing the cells

I want to create an On/Off button that will temporarily show which cells
contain formulas.

If someone knows a better way pls say so.

I thought of adding and then subtracting a fixed number from the
Interior.color property but I don't know how to retrieve the current value.

rngFormulas.Select
If flag = False Then
With Selection.Interior
.ColorIndex = ????? + 300000
End With
flag = True
Else
With Selection.Interior
.ColorIndex = ????? - 300000
End With
flag = False
End If

This doesn't incorporate your flag so adjust if you need that.

Select either a single cell to get formulas in whole sheet or a selection of
cells.

Sub ToggleFormulaColour()
Dim rng As Range, vFntClrIdx

On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo errH
If rng Is Nothing Then
MsgBox "No Formulas"
Else

vFntClrIdx = rng.Font.ColorIndex
If IsNull(vFntClrIdx) Then vFntClrIdx = -1

If vFntClrIdx > 0 Then
vFntClrIdx = xlAutomatic
Else
vFntClrIdx = 5 'blue in a default palette
End If

rng.Font.ColorIndex = vFntClrIdx
End If

errH:

End Sub

Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are
involved.

Also try Ctrl-`¬¦ the key under Esc.

Regards,
Peter T


"DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
news:8156FB93-13F4-4DA4-ABB9-D1ED7F7F5373@microsoft.com...
> I want to create an On/Off button that will temporarily show which cells
> contain formulas.
>
> If someone knows a better way pls say so.
>
> I thought of adding and then subtracting a fixed number from the
> Interior.color property but I don't know how to retrieve the current
value.
>
> rngFormulas.Select
> If flag = False Then
> With Selection.Interior
> .ColorIndex = ????? + 300000
> End With
> flag = True
> Else
> With Selection.Interior
> .ColorIndex = ????? - 300000
> End With
> flag = False
> End If
>

Peter I believe your approach assumes a single Font Color for the whole
range. I am asking for a way to change the background color (it is easier to
spot) back and forth regardless if it is the same for all cells or not.

That is why I am looking for a way to change each cell color individually.
Is this possible?

"Peter T" wrote:

> This doesn't incorporate your flag so adjust if you need that.
>
> Select either a single cell to get formulas in whole sheet or a selection of
> cells.
>
> Sub ToggleFormulaColour()
> Dim rng As Range, vFntClrIdx
>
> On Error Resume Next
> Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
> On Error GoTo errH
> If rng Is Nothing Then
> MsgBox "No Formulas"
> Else
>
> vFntClrIdx = rng.Font.ColorIndex
> If IsNull(vFntClrIdx) Then vFntClrIdx = -1
>
> If vFntClrIdx > 0 Then
> vFntClrIdx = xlAutomatic
> Else
> vFntClrIdx = 5 'blue in a default palette
> End If
>
> rng.Font.ColorIndex = vFntClrIdx
> End If
>
> errH:
>
> End Sub
>
> Note SpecialCells in VBA fails if a little over 8000 discontiguous areas are
> involved.
>
> Also try Ctrl-`¬¦ the key under Esc.
>
> Regards,
> Peter T
>
>
> "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> news:8156FB93-13F4-4DA4-ABB9-D1ED7F7F5373@microsoft.com...
> > I want to create an On/Off button that will temporarily show which cells
> > contain formulas.
> >
> > If someone knows a better way pls say so.
> >
> > I thought of adding and then subtracting a fixed number from the
> > Interior.color property but I don't know how to retrieve the current
> value.
> >
> > rngFormulas.Select
> > If flag = False Then
> > With Selection.Interior
> > .ColorIndex = ????? + 300000
> > End With
> > flag = True
> > Else
> > With Selection.Interior
> > .ColorIndex = ????? - 300000
> > End With
> > flag = False
> > End If
> >
>
>
>

Did you actually try the example.

If you want background fill change

vFntClrIdx = rng.Font.ColorIndex
to
vFntClrIdx = rng.Interior.ColorIndex

and xlAutomatic to xlNone

I don't understand why you want to process each cell individually

Regards,
Peter T

"DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
news:94822A8E-4DD7-4098-A305-EB38BFB53790@microsoft.com...
> Peter I believe your approach assumes a single Font Color for the whole
> range. I am asking for a way to change the background color (it is easier
to
> spot) back and forth regardless if it is the same for all cells or not.
>
> That is why I am looking for a way to change each cell color individually.
> Is this possible?
>
> "Peter T" wrote:
>
> > This doesn't incorporate your flag so adjust if you need that.
> >
> > Select either a single cell to get formulas in whole sheet or a
selection of
> > cells.
> >
> > Sub ToggleFormulaColour()
> > Dim rng As Range, vFntClrIdx
> >
> > On Error Resume Next
> > Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
> > On Error GoTo errH
> > If rng Is Nothing Then
> > MsgBox "No Formulas"
> > Else
> >
> > vFntClrIdx = rng.Font.ColorIndex
> > If IsNull(vFntClrIdx) Then vFntClrIdx = -1
> >
> > If vFntClrIdx > 0 Then
> > vFntClrIdx = xlAutomatic
> > Else
> > vFntClrIdx = 5 'blue in a default palette
> > End If
> >
> > rng.Font.ColorIndex = vFntClrIdx
> > End If
> >
> > errH:
> >
> > End Sub
> >
> > Note SpecialCells in VBA fails if a little over 8000 discontiguous areas
are
> > involved.
> >
> > Also try Ctrl-`¬¦ the key under Esc.
> >
> > Regards,
> > Peter T
> >
> >
> > "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> > news:8156FB93-13F4-4DA4-ABB9-D1ED7F7F5373@microsoft.com...
> > > I want to create an On/Off button that will temporarily show which
cells
> > > contain formulas.
> > >
> > > If someone knows a better way pls say so.
> > >
> > > I thought of adding and then subtracting a fixed number from the
> > > Interior.color property but I don't know how to retrieve the current
> > value.
> > >
> > > rngFormulas.Select
> > > If flag = False Then
> > > With Selection.Interior
> > > .ColorIndex = ????? + 300000
> > > End With
> > > flag = True
> > > Else
> > > With Selection.Interior
> > > .ColorIndex = ????? - 300000
> > > End With
> > > flag = False
> > > End If
> > >
> >
> >
> >

Because formula cells might have different background colors...

"Peter T" wrote:

> Did you actually try the example.
>
> If you want background fill change
>
> vFntClrIdx = rng.Font.ColorIndex
> to
> vFntClrIdx = rng.Interior.ColorIndex
>
> and xlAutomatic to xlNone
>
> I don't understand why you want to process each cell individually
>
> Regards,
> Peter T
>
> "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> news:94822A8E-4DD7-4098-A305-EB38BFB53790@microsoft.com...
> > Peter I believe your approach assumes a single Font Color for the whole
> > range. I am asking for a way to change the background color (it is easier
> to
> > spot) back and forth regardless if it is the same for all cells or not.
> >
> > That is why I am looking for a way to change each cell color individually.
> > Is this possible?
> >
> > "Peter T" wrote:
> >
> > > This doesn't incorporate your flag so adjust if you need that.
> > >
> > > Select either a single cell to get formulas in whole sheet or a
> selection of
> > > cells.
> > >
> > > Sub ToggleFormulaColour()
> > > Dim rng As Range, vFntClrIdx
> > >
> > > On Error Resume Next
> > > Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
> > > On Error GoTo errH
> > > If rng Is Nothing Then
> > > MsgBox "No Formulas"
> > > Else
> > >
> > > vFntClrIdx = rng.Font.ColorIndex
> > > If IsNull(vFntClrIdx) Then vFntClrIdx = -1
> > >
> > > If vFntClrIdx > 0 Then
> > > vFntClrIdx = xlAutomatic
> > > Else
> > > vFntClrIdx = 5 'blue in a default palette
> > > End If
> > >
> > > rng.Font.ColorIndex = vFntClrIdx
> > > End If
> > >
> > > errH:
> > >
> > > End Sub
> > >
> > > Note SpecialCells in VBA fails if a little over 8000 discontiguous areas
> are
> > > involved.
> > >
> > > Also try Ctrl-`¬¦ the key under Esc.
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > > "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> > > news:8156FB93-13F4-4DA4-ABB9-D1ED7F7F5373@microsoft.com...
> > > > I want to create an On/Off button that will temporarily show which
> cells
> > > > contain formulas.
> > > >
> > > > If someone knows a better way pls say so.
> > > >
> > > > I thought of adding and then subtracting a fixed number from the
> > > > Interior.color property but I don't know how to retrieve the current
> > > value.
> > > >
> > > > rngFormulas.Select
> > > > If flag = False Then
> > > > With Selection.Interior
> > > > .ColorIndex = ????? + 300000
> > > > End With
> > > > flag = True
> > > > Else
> > > > With Selection.Interior
> > > > .ColorIndex = ????? - 300000
> > > > End With
> > > > flag = False
> > > > End If
> > > >
> > >
> > >
> > >
>
>
>

Is there some other property you can change, eg pattern, font bold, .size
etc

In your OP you mentioned changing the colour index by some factor (not
300000 as colour index's are only in the region 1-56 and two -ve numbers),
if you have multiple format colours how would you differentiate which are
original & which temporarily changed.

Something along the lines of your objective is very doable but I can only
suggest consider the logic as to how you want to do that whilst retaining
the possibility to reset your original formats.

Regards,
Peter T

"DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
news:A7B82E51-D82A-46CB-8657-054E227F744D@microsoft.com...
> Because formula cells might have different background colors...
>
> "Peter T" wrote:
>
> > Did you actually try the example.
> >
> > If you want background fill change
> >
> > vFntClrIdx = rng.Font.ColorIndex
> > to
> > vFntClrIdx = rng.Interior.ColorIndex
> >
> > and xlAutomatic to xlNone
> >
> > I don't understand why you want to process each cell individually
> >
> > Regards,
> > Peter T
> >
> > "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> > news:94822A8E-4DD7-4098-A305-EB38BFB53790@microsoft.com...
> > > Peter I believe your approach assumes a single Font Color for the
whole
> > > range. I am asking for a way to change the background color (it is
easier
> > to
> > > spot) back and forth regardless if it is the same for all cells or
not.
> > >
> > > That is why I am looking for a way to change each cell color
individually.
> > > Is this possible?
> > >
> > > "Peter T" wrote:
> > >
> > > > This doesn't incorporate your flag so adjust if you need that.
> > > >
> > > > Select either a single cell to get formulas in whole sheet or a
> > selection of
> > > > cells.
> > > >
> > > > Sub ToggleFormulaColour()
> > > > Dim rng As Range, vFntClrIdx
> > > >
> > > > On Error Resume Next
> > > > Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
> > > > On Error GoTo errH
> > > > If rng Is Nothing Then
> > > > MsgBox "No Formulas"
> > > > Else
> > > >
> > > > vFntClrIdx = rng.Font.ColorIndex
> > > > If IsNull(vFntClrIdx) Then vFntClrIdx = -1
> > > >
> > > > If vFntClrIdx > 0 Then
> > > > vFntClrIdx = xlAutomatic
> > > > Else
> > > > vFntClrIdx = 5 'blue in a default palette
> > > > End If
> > > >
> > > > rng.Font.ColorIndex = vFntClrIdx
> > > > End If
> > > >
> > > > errH:
> > > >
> > > > End Sub
> > > >
> > > > Note SpecialCells in VBA fails if a little over 8000 discontiguous
areas
> > are
> > > > involved.
> > > >
> > > > Also try Ctrl-`¬¦ the key under Esc.
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > >
> > > > "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> > > > news:8156FB93-13F4-4DA4-ABB9-D1ED7F7F5373@microsoft.com...
> > > > > I want to create an On/Off button that will temporarily show which
> > cells
> > > > > contain formulas.
> > > > >
> > > > > If someone knows a better way pls say so.
> > > > >
> > > > > I thought of adding and then subtracting a fixed number from the
> > > > > Interior.color property but I don't know how to retrieve the
current
> > > > value.
> > > > >
> > > > > rngFormulas.Select
> > > > > If flag = False Then
> > > > > With Selection.Interior
> > > > > .ColorIndex = ????? + 300000
> > > > > End With
> > > > > flag = True
> > > > > Else
> > > > > With Selection.Interior
> > > > > .ColorIndex = ????? - 300000
> > > > > End With
> > > > > flag = False
> > > > > End If
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Did you actually try the example.

If you want background fill change

vFntClrIdx = rng.Font.ColorIndex
to
vFntClrIdx = rng.Interior.ColorIndex

and xlAutomatic to xlNone

I don't understand why you want to process each cell individually

Regards,
Peter T

"DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
news:94822A8E-4DD7-4098-A305-EB38BFB53790@microsoft.com...
> Peter I believe your approach assumes a single Font Color for the whole
> range. I am asking for a way to change the background color (it is easier
to
> spot) back and forth regardless if it is the same for all cells or not.
>
> That is why I am looking for a way to change each cell color individually.
> Is this possible?
>
> "Peter T" wrote:
>
> > This doesn't incorporate your flag so adjust if you need that.
> >
> > Select either a single cell to get formulas in whole sheet or a
selection of
> > cells.
> >
> > Sub ToggleFormulaColour()
> > Dim rng As Range, vFntClrIdx
> >
> > On Error Resume Next
> > Set rng = Selection.SpecialCells(xlCellTypeFormulas, 23)
> > On Error GoTo errH
> > If rng Is Nothing Then
> > MsgBox "No Formulas"
> > Else
> >
> > vFntClrIdx = rng.Font.ColorIndex
> > If IsNull(vFntClrIdx) Then vFntClrIdx = -1
> >
> > If vFntClrIdx > 0 Then
> > vFntClrIdx = xlAutomatic
> > Else
> > vFntClrIdx = 5 'blue in a default palette
> > End If
> >
> > rng.Font.ColorIndex = vFntClrIdx
> > End If
> >
> > errH:
> >
> > End Sub
> >
> > Note SpecialCells in VBA fails if a little over 8000 discontiguous areas
are
> > involved.
> >
> > Also try Ctrl-`¬¦ the key under Esc.
> >
> > Regards,
> > Peter T
> >
> >
> > "DoctorG" <DoctorG@discussions.microsoft.com> wrote in message
> > news:8156FB93-13F4-4DA4-ABB9-D1ED7F7F5373@microsoft.com...
> > > I want to create an On/Off button that will temporarily show which
cells
> > > contain formulas.
> > >
> > > If someone knows a better way pls say so.
> > >
> > > I thought of adding and then subtracting a fixed number from the
> > > Interior.color property but I don't know how to retrieve the current
> > value.
> > >
> > > rngFormulas.Select
> > > If flag = False Then
> > > With Selection.Interior
> > > .ColorIndex = ????? + 300000
> > > End With
> > > flag = True
> > > Else
> > > With Selection.Interior
> > > .ColorIndex = ????? - 300000
> > > End With
> > > flag = False
> > > End If
> > >
> >
> >
> >

Hey Doc

Very easy... just create the buttons, and in the code module paste this
line for viewing all the cells that contain formulas...

ActiveWindow.DisplayFormulas = True

To hide the formulas, just insert this line...

ActiveWindow.DisplayFormulas = False

Let me know if you win.


DoctorG wrote:
> I want to create an On/Off button that will temporarily show which cells
> contain formulas.
>
> If someone knows a better way pls say so.
>
> I thought of adding and then subtracting a fixed number from the
> Interior.color property but I don't know how to retrieve the current value.
>
> rngFormulas.Select
> If flag = False Then
> With Selection.Interior
> .ColorIndex = ????? + 300000
> End With
> flag = True
> Else
> With Selection.Interior
> .ColorIndex = ????? - 300000
> End With
> flag = False
> End If

Thanks for this tip.

I am already aware of the Display Formulas Option but I wanted something
that I could use in programming. I was presented with a 7500 line spreadsheet
with mixed formula and text/value content. It was a nightmare just looking
at. The changed background would "position" the eye easier to the cells
needing attention. Not to mention the fact that I could trap a certain
attribute or property and filter the column. I needed knowledge on
conditional range and property handling.

Thanks anyway!

"gonecrazybacksoon@gmail.com" wrote:

> Hey Doc
>
> Very easy... just create the buttons, and in the code module paste this
> line for viewing all the cells that contain formulas...
>
> ActiveWindow.DisplayFormulas = True
>
> To hide the formulas, just insert this line...
>
> ActiveWindow.DisplayFormulas = False
>
> Let me know if you win.
>
>
> DoctorG wrote:
> > I want to create an On/Off button that will temporarily show which cells
> > contain formulas.
> >
> > If someone knows a better way pls say so.
> >
> > I thought of adding and then subtracting a fixed number from the
> > Interior.color property but I don't know how to retrieve the current value.
> >
> > rngFormulas.Select
> > If flag = False Then
> > With Selection.Interior
> > .ColorIndex = ????? + 300000
> > End With
> > flag = True
> > Else
> > With Selection.Interior
> > .ColorIndex = ????? - 300000
> > End With
> > flag = False
> > End If
>
>