Formula cells

G

glen.e.mettler

I have modified Gord Dibben's macro from a few days ago to color the
cells in the workbook that contain formulas. However, after I do that
I want to return the cells to the original color. I toyed with the
idea of creating an elaborate array to capture the original color and
then rerun the macro using that color. However, in one VBA book I
have it talks about the computing overhead the Redim takes and
suggests using a Collection (but doesn't elaborate).

Anybody have a clue on how best to do this?

Glen
 
B

Bernie Deitrick

Glen,

Since you didn't post Gord's macro, I used my own to show how to do it. The following macros assume
that there are cells with formulas on the activesheet: run "ShadeFormulas" to shade them all red,
then run "RestoreFormulaShading" to restore the shading. Note that the record will be lost if the
workbook is closed in between.

Note: redim doesn't take a lot of resources - it's not like our computers are running on vacuum
tubes and punch cards...

HTH,
Bernie
MS Excel MVP


Option Explicit
Dim myCI() As String
Dim myFCount As Long
Dim myCell As Range
Dim myCount As Integer

Sub ShadeFormulas()
myFCount = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Count

ReDim myCI(1 To 2, 1 To myFCount)

myCount = 1

For Each myCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
myCI(1, myCount) = myCell.Address
myCI(2, myCount) = myCell.Interior.ColorIndex
myCell.Interior.ColorIndex = 3
myCount = myCount + 1
Next myCell
End Sub

Sub RestoreFormulaShading()
For myCount = 1 To myFCount
Range(myCI(1, myCount)).Interior.ColorIndex = _
myCI(2, myCount)
Next myCount

End Sub
 
G

glen.e.mettler

Glen,

Since you didn't post Gord's macro, I used my own to show how to do it. The following macros assume
that there are cells with formulas on the activesheet: run "ShadeFormulas" to shade them all red,
then run "RestoreFormulaShading" to restore the shading. Note that the record will be lost if the
workbook is closed in between.

Note: redim doesn't take a lot of resources - it's not like our computers are running on vacuum
tubes and punch cards...

HTH,
Bernie
MS Excel MVP

Option Explicit
Dim myCI() As String
Dim myFCount As Long
Dim myCell As Range
Dim myCount As Integer

Sub ShadeFormulas()
myFCount = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Count

ReDim myCI(1 To 2, 1 To myFCount)

myCount = 1

For Each myCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
myCI(1, myCount) = myCell.Address
myCI(2, myCount) = myCell.Interior.ColorIndex
myCell.Interior.ColorIndex = 3
myCount = myCount + 1
Next myCell
End Sub

Sub RestoreFormulaShading()
For myCount = 1 To myFCount
Range(myCI(1, myCount)).Interior.ColorIndex = _
myCI(2, myCount)
Next myCount

End Sub
Click to expand...
Perfect!! just what I was looking for.

Thanks

Glen
 
Top