PDA

View Full Version : a certain type of formula



lior03
07-31-2006, 10:02 AM
hello
is it possible to select or color a certain type of formulas within a range.
can i select all cells in a range containing a sum formula or just the cells containing average formula.
thanks

Bob Phillips
07-31-2006, 10:49 AM
Not without a ton of work I think.

LWuerth
07-31-2006, 03:39 PM
Wasn't THAT much work!

Sub Test()
Dim c As Range
For Each c In Selection
If UCase(Mid(c.Formula, 1, 4)) = "=SUM" Then
With c.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
Next c
End Sub

Bob Phillips
07-31-2006, 03:47 PM
That is because you are being too simplistic.

What about

=IF(A1=17,VLOOKUP(A1,M1:N20,2,FALSE),SUM(A1:A20))

that's a SUM formula in my book, and I can think of loads far mor complex than that. And if you just check for SUM rather than =SUMm, how about

=IF(A1=10,"SUMMARY DATA","")

and we haven't even started on the other functions yet.