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

  • Thread starter Thread starter everythingautomotive
  • Start date Start date
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?
 
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?
Click to expand...

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
 
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
 
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 -
Click to expand...

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.
 
You could get all the cells at once, too:
Click to expand...

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
 
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?
Click to expand...

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
 
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
Click to expand...

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).
 
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
Click to expand...

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).
 
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
Click to expand...

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?
 
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).
Click to expand...

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
 
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
Click to expand...

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.
 
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?
Click to expand...

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
 
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
Click to expand...

Absolute Genious!!!

That works FANTASTIC. Thank you so much.
 
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
 
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.
 
Ignore this.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change Color of cell 3
Excel VBA 1
OR formula, ????? 2
Counting Cell Color? 2
Apply color of one cell to other 2
Counting Cells with colors 7
Conditional format color change 2
Conditional Formating with IF and AND formula 2

Back
Top