E
everythingautomotive
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?
or are part of a formula???
if so, how would i do that?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
change color of all cells with formula or are part of a formula
B
Bob Phillips
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)
http://www.contextures.com/xlCondFormat01.html
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
R
Ron Rosenfeld
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
D
Dave Peterson
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
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
E
everythingautomotive
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)
- 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.
R
Ron Rosenfeld
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.Color = vbYellow
End Sub
==========================
--ron
R
Ron Rosenfeld
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
E
everythingautomotive
Neat. I didn't realize that.
You could make the routine even shorter:
=========================
Sub ColorFormulas()
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas).Interior.Color = 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).
E
everythingautomotive
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).
E
everythingautomotive
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?
R
Ron Rosenfeld
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
E
everythingautomotive
You probably were running my first recommendation, and not the most recentone:
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.
R
Ron Rosenfeld
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
E
everythingautomotive
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
Absolute Genious!!!
That works FANTASTIC. Thank you so much.
R
Ron Rosenfeld
Absolute Genious!!!
That works FANTASTIC. Thank you so much.
You're welcome. Glad to help. Thanks for the feedback.
--ron
G
Gord Dibben
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
Dave's always reminding me also that you don't need the For Each.......Next in
most cases.
I am slowly learning<g>
Gord
D
Dave Peterson
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.
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.
D
Dave Peterson
Ignore this.
I was thinking (mistakenly) that the .precedents would include cells in
different workbooks or different worksheets.
I was thinking (mistakenly) that the .precedents would include cells in
different workbooks or different worksheets.
R
Ron Rosenfeld
I am slowly learning<g>
It takes a while for me, too! Glad to see I'm not alone.
--ron
D
Dave Peterson
I wish I were. I looped through my second suggestion!