highlight all cells which use SUMPRODUCT in their formulas....
 

I have a large spreadsheet, comprising a range of about 100000 individual
cells, a number of which cells are running various flavors of SUMPRODUCT
calculations. Is there a macro I can write which would scan all the cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000 individual
cells, a number of which cells are running various flavors of SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Wow, that's brilliant, thanks.

Dave
--
Brevity is the soul of wit.


"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000 individual
cells, a number of which cells are running various flavors of SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000 individual
cells, a number of which cells are running various flavors of SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

This will make cell with =SUMPRODUCT red
If you want another 'hightlight' just record a macro as you format a cell
manually and get code from it
Adjust first line of code as needed
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Sub myformat()
Set myrange = Range("A1:C20")
For Each mycell In myrange
mytest = mycell.Formula
mytest = Mid(mytest, 1, 11)
If mytest = "=SUMPRODUCT" Then
With mycell.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next
End Sub




"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000 individual
cells, a number of which cells are running various flavors of SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Alternatively place the UDF below in a standard module.
Select the range of cells to test
Use Conditional Formatting with
Cell Formula is =MYFORMULA(A1)=TRUE
OF course, the cell reference must be to you first cell
best wishes

Function myformula(mycell)
mytest = mycell.Formula
mytest = Mid(mytest, 1, 11)
If mytest = "=SUMPRODUCT" Then
myformula = True
Else
myformula = False
End If
End Function

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000 individual
cells, a number of which cells are running various flavors of SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Thank-you Chuck :-)

Bob

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
cell.Interior.ColorIndex = -(cell.Formula Like "*SUMPRODUCT*") * 38
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

That's nice Bob, but more difficult for me to understand......and besides,
where I come from in the hills of Virginia, "shorter" is not usually
considered "better", if you know what I mean.....<G

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
cell.Interior.ColorIndex = -(cell.Formula Like "*SUMPRODUCT*") * 38
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan all the
cells'
formulas and highlight those cells whose formulas contain SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Ah, but you were smart enough to move (San Diego isn't it?).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
That's nice Bob, but more difficult for me to understand......and besides,
where I come from in the hills of Virginia, "shorter" is not usually
considered "better", if you know what I mean.....<G

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
cell.Interior.ColorIndex = -(cell.Formula Like "*SUMPRODUCT*") *
38
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan all
the
cells'
formulas and highlight those cells whose formulas contain
SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Touche'.............actually St. Petersburg, Florida now........I went
through Boot Camp in the Marine Corps in '55-56 in San Diego...........

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Ah, but you were smart enough to move (San Diego isn't it?).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
That's nice Bob, but more difficult for me to understand......and besides,
where I come from in the hills of Virginia, "shorter" is not usually
considered "better", if you know what I mean.....<G

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
cell.Interior.ColorIndex = -(cell.Formula Like "*SUMPRODUCT*") *
38
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan all
the
cells'
formulas and highlight those cells whose formulas contain
SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

So how did I know about San Diego? Odd!

I've been to St Petersburg, stayed up the cost a bit (Port Richey I think it
was), but I stayed a night at a hotel by the beach. It was the worst summer
Florida had for decades, and I was there :-(.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Touche'.............actually St. Petersburg, Florida now........I went
through Boot Camp in the Marine Corps in '55-56 in San Diego...........

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Ah, but you were smart enough to move (San Diego isn't it?).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
That's nice Bob, but more difficult for me to understand......and
besides,
where I come from in the hills of Virginia, "shorter" is not usually
considered "better", if you know what I mean.....<G

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
cell.Interior.ColorIndex = -(cell.Formula Like
"*SUMPRODUCT*") *
38
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan
all
the
cells'
formulas and highlight those cells whose formulas contain
SUMPRODUCT?

Dave
--
Brevity is the soul of wit.

highlight all cells which use SUMPRODUCT in their formulas....
 

Probably just a regular ordinary "time-warp"...........

With my medications.select
.get a lot of that
.<G
End with

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

So how did I know about San Diego? Odd!

I've been to St Petersburg, stayed up the cost a bit (Port Richey I think it
was), but I stayed a night at a hotel by the beach. It was the worst summer
Florida had for decades, and I was there :-(.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Touche'.............actually St. Petersburg, Florida now........I went
through Boot Camp in the Marine Corps in '55-56 in San Diego...........

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Ah, but you were smart enough to move (San Diego isn't it?).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
That's nice Bob, but more difficult for me to understand......and
besides,
where I come from in the hills of Virginia, "shorter" is not usually
considered "better", if you know what I mean.....<G

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote:

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
cell.Interior.ColorIndex = -(cell.Formula Like
"*SUMPRODUCT*") *
38
Next cell

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"CLR" wrote in message
...
Incredibly COOL Bob..........a keeper for sure.

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote:

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)
If cell.Formula Like "*SUMPRODUCT*" Then
cell.Interior.ColorIndex = 38
End If
Next cell

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Dave F" wrote in message
...
I have a large spreadsheet, comprising a range of about 100000
individual
cells, a number of which cells are running various flavors of
SUMPRODUCT
calculations. Is there a macro I can write which would scan
all
the
cells'
formulas and highlight those cells whose formulas contain
SUMPRODUCT?

Dave
--
Brevity is the soul of wit.