change color of all cells with formula or are part of a formula
 

is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

change color of all cells with formula or are part of a formula
 

Yes, use that formula in conditional formatting. See
http://www.contextures.com/xlCondFormat01.html

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 09:37:27 -0800 (PST), wrote:

is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

You could use a macro. Run the macro below when the Sheet you wish to modify
is active:

=======================
Option Explicit
Sub ColorFormulas()
Dim c As Range
Dim rng As Range
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
For Each c In rng
c.Interior.Color = vbYellow
Next c
End Sub
======================
--ron

change color of all cells with formula or are part of a formula
 

You could get all the cells at once, too:

Option Explicit
Sub ColorFormulas2()
Dim rng As Range

set rng = nothing
on error resume next 'just in case there are no formulas
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
on error goto 0

if rng is nothing then
'do nothing
else
rng.Interior.Color = vbYellow
end if
End Sub

Ron Rosenfeld wrote:

On Sun, 27 Jan 2008 09:37:27 -0800 (PST), wrote:

is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

You could use a macro. Run the macro below when the Sheet you wish to modify
is active:

=======================
Option Explicit
Sub ColorFormulas()
Dim c As Range
Dim rng As Range
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
For Each c In rng
c.Interior.Color = vbYellow
Next c
End Sub
======================
--ron

--

Dave Peterson

change color of all cells with formula or are part of a formula
 

On Jan 27, 12:43*pm, "Bob Phillips" wrote:
Yes, use that formula in conditional formatting. Seehttp://www.contextures..com/xlCondFormat01.html

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

...



is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?- Hide quoted text -

- Show quoted text -

I think maybe I am saying it wrong. I want for excel to change the
color of all the cells in the spreadsheet that have a formula in them
or are referenced in a formula. I want the rest of the cells to stay
normal if they don't contain one of the two.??? Does that make sense?
Maybe I can show an example somehow.

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 11:52:06 -0600, Dave Peterson
wrote:

You could get all the cells at once, too:

Neat. I didn't realize that.

You could make the routine even shorter:

=========================
Sub ColorFormulas()
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas).Interior.Co lor = vbYellow
End Sub
==========================


--ron

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 09:37:27 -0800 (PST), wrote:

is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

As I read this, what do you mean by cells that "are part of a formula"?

If you mean what I think, then perhaps:

=========================
Sub ColorFormulas()
On Error Resume Next
With Cells.SpecialCells(xlCellTypeFormulas)
.Interior.Color = vbYellow
.Precedents.Interior.Color = vbRed
End With
End Sub
============================

Of course, as written, if you make changes, the already colored cells may not
change.

It would be simplest to first set the format to "none" for the worksheet, and
then just color the formulas and precedents. But this may not be appropriate.

==============================
Sub ColorFormulas()
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
.Interior.Color = vbYellow
.Precedents.Interior.Color = vbRed
End With
End Sub
===============================

--ron

change color of all cells with formula or are part of a formula
 

On Jan 27, 1:00*pm, Ron Rosenfeld wrote:
On Sun, 27 Jan 2008 11:52:06 -0600, Dave Peterson
wrote:

You could get all the cells at once, too:

Neat. *I didn't realize that.

You could make the routine even shorter:

=========================
Sub ColorFormulas()
On Error Resume Next
* Cells.SpecialCells(xlCellTypeFormulas).Interior.Co lor = vbYellow
End Sub
==========================

--ron

If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A2 &
B4 all to be shaded. maybe I am doing something wrong but all of this
stuff when I run the macros only shades D11 (the one with the actual
formula).

change color of all cells with formula or are part of a formula
 

On Jan 27, 1:09*pm, Ron Rosenfeld wrote:
On Sun, 27 Jan 2008 09:37:27 -0800 (PST), wrote:
is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

As I read this, what do you mean by cells that "are part of a formula"?

If you mean what I think, then perhaps:

=========================
Sub ColorFormulas()
On Error Resume Next
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
End Sub
============================

Of course, as written, if you make changes, the already colored cells may not
change.

It would be simplest to first set the format to "none" for the worksheet, and
then just color the formulas and precedents. *But this may not be appropriate.

==============================
Sub ColorFormulas()
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
End Sub
===============================

--ron

If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A3 &
B4 all to be shaded. maybe I am doing something wrong but all of
this
stuff when I run the macros only shades D11 (the one with the actual
formula).

change color of all cells with formula or are part of a formula
 

On Jan 27, 1:09*pm, Ron Rosenfeld wrote:
On Sun, 27 Jan 2008 09:37:27 -0800 (PST), wrote:
is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

As I read this, what do you mean by cells that "are part of a formula"?

If you mean what I think, then perhaps:

=========================
Sub ColorFormulas()
On Error Resume Next
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
End Sub
============================

Of course, as written, if you make changes, the already colored cells may not
change.

It would be simplest to first set the format to "none" for the worksheet, and
then just color the formulas and precedents. *But this may not be appropriate.

==============================
Sub ColorFormulas()
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
End Sub
===============================

--ron

that definately works. if i wanted the sheet to change the cell
colors as i am entering data is there a way to do that? or do i have
to keep running the macro over every so often?

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 10:13:16 -0800 (PST), wrote:

If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A3 &
B4 all to be shaded. maybe I am doing something wrong but all of
this
stuff when I run the macros only shades D11 (the one with the actual
formula).

You probably were running my first recommendation, and not the most recent one:

Sub ColorFormulas()
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
.Interior.Color = vbYellow
.Precedents.Interior.Color = vbRed
End With
End Sub
--ron

change color of all cells with formula or are part of a formula
 

On Jan 27, 1:18*pm, Ron Rosenfeld wrote:
On Sun, 27 Jan 2008 10:13:16 -0800 (PST), wrote:
If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A3 &
B4 all to be shaded. *maybe I am doing something wrong but all of
this
stuff when I run the macros only shades D11 (the one with the actual
formula).

You probably were running my first recommendation, and not the most recent one:

Sub ColorFormulas()
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
End Sub
--ron

no i did both. the second is definately the better of the two but it
doesn't update automatically as i am entering new formulas. in order
for it to change the cell color it seems i have to run the macro
again. i do really appreciate all your help and quick response with
this.

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 10:17:06 -0800 (PST), wrote:

that definately works. if i wanted the sheet to change the cell
colors as i am entering data is there a way to do that? or do i have
to keep running the macro over every so often?

You could try using a worksheet selection_change event. But, depending on the
size of your worksheet, it might slow things down.

For example, right click on the sheet tab and select View Code. Then paste
this into the window that opens:

-----------------------
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
.Interior.Color = vbYellow
.Precedents.Interior.Color = vbRed
End With
Application.EnableEvents = True
End Sub
------------------------
--ron

change color of all cells with formula or are part of a formula
 

On Jan 27, 1:25*pm, Ron Rosenfeld wrote:
On Sun, 27 Jan 2008 10:17:06 -0800 (PST), wrote:
that definately works. *if i wanted the sheet to change the cell
colors as i am entering data is there a way to do that? *or do i have
to keep running the macro over every so often?

You could try using a worksheet selection_change event. *But, depending on the
size of your worksheet, it might slow things down.

For example, right click on the sheet tab and select View Code. *Then paste
this into the window that opens:

-----------------------
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
Application.EnableEvents = True
End Sub
------------------------
--ron

Absolute Genious!!!

That works FANTASTIC. Thank you so much.

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 10:31:15 -0800 (PST), wrote:

On Jan 27, 1:25*pm, Ron Rosenfeld wrote:
On Sun, 27 Jan 2008 10:17:06 -0800 (PST), wrote:
that definately works. *if i wanted the sheet to change the cell
colors as i am entering data is there a way to do that? *or do i have
to keep running the macro over every so often?

You could try using a worksheet selection_change event. *But, depending on the
size of your worksheet, it might slow things down.

For example, right click on the sheet tab and select View Code. *Then paste
this into the window that opens:

-----------------------
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
* * .Interior.Color = vbYellow
* * .Precedents.Interior.Color = vbRed
End With
Application.EnableEvents = True
End Sub
------------------------
--ron

Absolute Genious!!!

That works FANTASTIC. Thank you so much.

You're welcome. Glad to help. Thanks for the feedback.
--ron

change color of all cells with formula or are part of a formula
 

Ron

Dave's always reminding me also that you don't need the For Each.......Next in
most cases.

I am slowly learning<g


Gord

On Sun, 27 Jan 2008 13:00:09 -0500, Ron Rosenfeld
wrote:

On Sun, 27 Jan 2008 11:52:06 -0600, Dave Peterson
wrote:

You could get all the cells at once, too:

Neat. I didn't realize that.

You could make the routine even shorter:

=========================
Sub ColorFormulas()
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas).Interior.Co lor = vbYellow
End Sub
==========================


--ron

change color of all cells with formula or are part of a formula
 

Back to looping through the cells with formulas.

This will loop through all the formulas on the worksheet and look for precedents
on the same worksheet.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myFormRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = ActiveSheet

'get the cells that have dependent cells
Set myFormRng = Nothing
On Error Resume Next
Set myFormRng = wks.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myFormRng Is Nothing Then
'do nothing
Else
'color the formulas
myFormRng.Interior.Color = vbYellow
For Each myCell In myFormRng.Cells
If myCell.Precedents Is Nothing Then
'skip it
Else
For Each myArea In myCell.Precedents.Areas
If myArea.Parent.Range("a1").Address(external:=True) _
= myCell.Parent.Range("a1").Address(external:=True) Then
myArea.Interior.Color = vbYellow
End If
Next myArea
End If
Next myCell
End If

End Sub

If you're really looking to trace your formulas, you may want to look at Jan
Karel Pieterse's Reference Tree analyzer:
http://www.jkp-ads.com/RefTreeAnalyser.asp

He offers both a demo (free) version and a pay for version. Those are described
on that site.



wrote:

<snipped
If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A2 &
B4 all to be shaded. maybe I am doing something wrong but all of this
stuff when I run the macros only shades D11 (the one with the actual
formula).

--

Dave Peterson

change color of all cells with formula or are part of a formula
 

Ignore this.

I was thinking (mistakenly) that the .precedents would include cells in
different workbooks or different worksheets.

Dave Peterson wrote:

Back to looping through the cells with formulas.

This will loop through all the formulas on the worksheet and look for precedents
on the same worksheet.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myFormRng As Range
Dim myArea As Range
Dim wks As Worksheet

Set wks = ActiveSheet

'get the cells that have dependent cells
Set myFormRng = Nothing
On Error Resume Next
Set myFormRng = wks.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myFormRng Is Nothing Then
'do nothing
Else
'color the formulas
myFormRng.Interior.Color = vbYellow
For Each myCell In myFormRng.Cells
If myCell.Precedents Is Nothing Then
'skip it
Else
For Each myArea In myCell.Precedents.Areas
If myArea.Parent.Range("a1").Address(external:=True) _
= myCell.Parent.Range("a1").Address(external:=True) Then
myArea.Interior.Color = vbYellow
End If
Next myArea
End If
Next myCell
End If

End Sub

If you're really looking to trace your formulas, you may want to look at Jan
Karel Pieterse's Reference Tree analyzer:
http://www.jkp-ads.com/RefTreeAnalyser.asp

He offers both a demo (free) version and a pay for version. Those are described
on that site.

wrote:

<snipped
If D11 has formula that says "=A1+A3+B4", I want cells D11, A1, A2 &
B4 all to be shaded. maybe I am doing something wrong but all of this
stuff when I run the macros only shades D11 (the one with the actual
formula).

--

Dave Peterson

--

Dave Peterson

change color of all cells with formula or are part of a formula
 

On Sun, 27 Jan 2008 11:38:28 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

I am slowly learning<g

It takes a while for me, too! Glad to see I'm not alone.
--ron

change color of all cells with formula or are part of a formula
 

I wish I were. I looped through my second suggestion!

Gord Dibben wrote:

Ron

Dave's always reminding me also that you don't need the For Each.......Next in
most cases.

I am slowly learning<g

Gord

On Sun, 27 Jan 2008 13:00:09 -0500, Ron Rosenfeld
wrote:

On Sun, 27 Jan 2008 11:52:06 -0600, Dave Peterson
wrote:

You could get all the cells at once, too:

Neat. I didn't realize that.

You could make the routine even shorter:

=========================
Sub ColorFormulas()
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas).Interior.Co lor = vbYellow
End Sub
==========================


--ron

--

Dave Peterson