Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
How do I copy only cells with formulas in another row? | Excel Worksheet Functions | |||
how do i protect cells in a shared worksheet | Excel Discussion (Misc queries) | |||
how get rid of cells with unused formulas | Excel Discussion (Misc queries) | |||
pasting or moving formula cells without updating formulas | Excel Discussion (Misc queries) |