find text in a formula

J

Joker

ok, I have a sheet that has two basic types of formulas:

=U10-P10
=U3-SUM(P3:p5)

The simpler one always stays the same relative to the row. The one with the
sum function however can be 2 or more rows. I want to be able to highlight
the ones that have the Sum function in it. I have tried all kinds of things
with conditional formatting, but nothing seems to work. Any ideas?
 
P

Pete_UK

If you highlight the complete worksheet by CTRL-A, you can then use
Find & Replace (CTRL-H) to replace "SUM(" with something ridiculous
like "xyz" (without the quotes). This will cause all the cells which
had a formula including SUM( to show errors (probably #NAME?). You
could then manually change the background of those cells which show the
error to (say) bright yellow and then reverse the process - CTRL-A
followed by CTRL-H and replace xyz with SUM(. I'm not sure how many
formulae you have and whether this would take you too long, but you
might like to try it.

Hope this helps.

Pete
 
K

kletcho

Open the visual basic editor (tools -- Macros -- Visual Basic Editor).
Insert a new module (Insert - Module), you will see it appear on the
left hand of your screen labeled Module1. Insert this code into the
module:

Public Sub SelectFunction()
Dim rngHasFunction As Range
Dim blnFirst As Boolean
Dim strFunction As String

'fill whatever function you are looking for here
strFunction = "SUM"

'cycle through each cell selected
For Each rng In Selection
With rng
'look for SUM formula
If InStr(1, .Formula, strFunction) > 0 Then
If Not blnFirst Then
'if first cell with the function set up the new
range
Set rngHasFunction = rng
blnFirst = Not blnFirst
Else
'add cell to range of cells with the function
Set rngHasFunction = Union(rngHasFunction, rng)
End If
End If
End With
Next rng
'Select all cells that contain the function
rngHasFunction.Select
End Sub

Now you can use this macro from the list of macros (tools -- macros --
play) and play it whenever you want. Simply play it, then perform
whatever formatting you want while all the cells with SUM are
highlighted.
 
Top