Conditional formatting based on the specific TYPE of formula in a cell (e.g. flat-lining versus summing)

[fieryred]

It seems to be commonly known that the Get.Cell function can be used to format (shade) cells conditionally based on whether they contain a formula or just a value (explained here: http://j-walk.com/ss/excel/usertips/tip045.htm).

Can anybody figure out how to take this one step further and format cells differently depending on the nature of the formula itself? For instance, if certain rows of data are forecast over time according to a sum formula and other rows are forecasted by simply "flat-lining" (e.g. the formula in B1 is "=A1" and the formula in C1 is "=B1") can the 2 be conditionally formatted differently?

This would be very helpful to clarify large sheets used in reporting and forecasting, but surprisingly I can't seem to find info on anyone else trying to do this. Thanks for any help or suggestions you can offer!

(Get.Cell is explained in more detail on this posting:
http://www.mrexcel.com/forum/showthread.php?t=20611)

 

[p45cal]

I can't think of a way by extending GET.CELL.
A start using another method though:
Cell N44 below is the only cell without a formula in.
So a cell with a =SUM in it is coloured orange, a cell with just a formula is coloured blue, and all other cells not affected.
<embed src="http://www.box.net/embed/kr053o4oh8fapp1.swf" wmode="opaque" type="application/x-shockwave-flash" allowfullscreen="true" allowscriptaccess="always" height="400" width="466">

Excel Workbook
	N	O
43	N28	1
44	N28	0
45	0	2
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N43	1. / Formula is =hasSum(N43)=2	Abc
N43	2. / Formula is =hasSum(N43)=1	Abc
O43	1. / Formula is =hasSum(O43)=2	Abc
O43	2. / Formula is =hasSum(O43)=1	Abc
N44	1. / Formula is =hasSum(N43)=2	Abc
N44	2. / Formula is =hasSum(N43)=1	Abc
O44	1. / Formula is =hasSum(O43)=2	Abc
O44	2. / Formula is =hasSum(O43)=1	Abc
N45	1. / Formula is =hasSum(N43)=2	Abc
N45	2. / Formula is =hasSum(N43)=1	Abc
O45	1. / Formula is =hasSum(O43)=2	Abc
O45	2. / Formula is =hasSum(O43)=1	Abc

backed up by a udf:

Function hasSum(rng)
'returns 0 for cell has no formula
'1 for cell has formula but no '=SUM' in the formula
'2 for cellhas a formula with '=SUM' in it.
hasSum = 0
If rng.hasformula Then
  hasSum = 1
  hasSum = hasSum + IIf(InStr(1, UCase(rng.Formula), "=SUM", vbTextCompare) > 0, 1, 0)
End If
End Function

 

[fieryred]

Thanks p45cal for the thorough, well documented response! The reason I mentioned GET.CELL in my original post was because of the significant performance advantages over UDFs.

I played around with it some more and came up with this function, which can be defined as a constant, say "Flatline" with Insert,Name,Define and then successfully used as a conditional formula to accomplish what I was looking to do.

=GET.CELL(6,INDIRECT("rc",FALSE))=CONCATENATE("=",ADDRESS(GET.CELL(2,OFFSET(INDIRECT("RC",FALSE),0,-1)),GET.CELL(3,OFFSET(INDIRECT("RC",FALSE),0,-1)),4))

The GET.CELL(6,INDIRECT("rc",FALSE)) part just returns the formula of the current cell.

The GET.CELL(2,OFFSET(INDIRECT("RC",FALSE),0,-1) and GET.CELL(3,OFFSET(INDIRECT("RC",FALSE),0,-1) parts return the row and column of the cell immediately to the left.

The concatenate and address function with "4" operator just convert the GET.CELL results to a comparable format and the "=" operator obviously does the comparison to return a TRUE or FALSE that can be used in a conditional formatting box.