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

D

Dave F

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
 
B

Bob Phillips

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
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)
 
B

Bernard Liengme

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
 
B

Bernard Liengme

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
 
B

Bob Phillips

Just for fun, even shorter

Sub ShowSUMPRODUCTs()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
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)
 
C

CLR

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
 
B

Bob Phillips

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)
 
C

CLR

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
 
B

Bob Phillips

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)
 
C

CLR

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

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

Vaya con Dios,
Chuck, CABGx3
 
Top