Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
I want to set conditional format so that it show when a cell contains a
formula and when a cell contains a value... This would be most valuable to use when user needs to input values.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
You need to create a UDF and use that
Function IsFormula(rng As Range) If rng.Count 1 Then IsFormula = CvErr(xlErrRef) Else IsFormula = rng.HasFormula End If End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gerrit" <Gerrit @discussions.microsoft.com wrote in message ... I want to set conditional format so that it show when a cell contains a formula and when a cell contains a value... This would be most valuable to use when user needs to input values.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
Gerrit, oddly enough, i am trying to do the same today, just about to post
question wheni read yours. We have tried isref & isnumber but that doesnt work, have you had any luck yet? Steve "Gerrit" wrote: I want to set conditional format so that it show when a cell contains a formula and when a cell contains a value... This would be most valuable to use when user needs to input values.. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
Bob, not my initial query but thanks, spot on.
Steve "Bob Phillips" wrote: You need to create a UDF and use that Function IsFormula(rng As Range) If rng.Count 1 Then IsFormula = CvErr(xlErrRef) Else IsFormula = rng.HasFormula End If End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gerrit" <Gerrit @discussions.microsoft.com wrote in message ... I want to set conditional format so that it show when a cell contains a formula and when a cell contains a value... This would be most valuable to use when user needs to input values.. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
Great, maybe a 2-for-1 <vbg
Bob "Steve" wrote in message ... Bob, not my initial query but thanks, spot on. Steve "Bob Phillips" wrote: You need to create a UDF and use that Function IsFormula(rng As Range) If rng.Count 1 Then IsFormula = CvErr(xlErrRef) Else IsFormula = rng.HasFormula End If End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gerrit" <Gerrit @discussions.microsoft.com wrote in message ... I want to set conditional format so that it show when a cell contains a formula and when a cell contains a value... This would be most valuable to use when user needs to input values.. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
I'm still a little confused as to how you then apply your UDF to a conditional format. The conditional format that I'm interested in applies only when a cell contains a formula; I'm not worried about whether it's a number or not. Do you still need a UDF since you only have one condition now? I'm new to conditional formatting, so thanks in advance for any help. -- mworth01 ------------------------------------------------------------------------ mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991 View this thread: http://www.excelforum.com/showthread...hreadid=512585 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
Yes you need the UDF as it is that that recognises a formula.
Add the UDF to your workbook. Select your cells, I'm assuming that you start at A1 In CF change Condition 1 To Formula Is Add =IsFormula(A1) Set your format OK -- HTH Bob Phillips (remove nothere from email address if mailing direct) "mworth01" wrote in message ... I'm still a little confused as to how you then apply your UDF to a conditional format. The conditional format that I'm interested in applies only when a cell contains a formula; I'm not worried about whether it's a number or not. Do you still need a UDF since you only have one condition now? I'm new to conditional formatting, so thanks in advance for any help. -- mworth01 ------------------------------------------------------------------------ mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991 View this thread: http://www.excelforum.com/showthread...hreadid=512585 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
Bob you opened a new world for me, Thanks a million!!
The UDF and the conditional Format works excellant on my computer (excell 2003).. For some strange reason I can't get it going on other computers also 2003... Went through the same drills (creating a UDF) but no luck... Gerrit "Bob Phillips" wrote: Yes you need the UDF as it is that that recognises a formula. Add the UDF to your workbook. Select your cells, I'm assuming that you start at A1 In CF change Condition 1 To Formula Is Add =IsFormula(A1) Set your format OK -- HTH Bob Phillips (remove nothere from email address if mailing direct) "mworth01" wrote in message ... I'm still a little confused as to how you then apply your UDF to a conditional format. The conditional format that I'm interested in applies only when a cell contains a formula; I'm not worried about whether it's a number or not. Do you still need a UDF since you only have one condition now? I'm new to conditional formatting, so thanks in advance for any help. -- mworth01 ------------------------------------------------------------------------ mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991 View this thread: http://www.excelforum.com/showthread...hreadid=512585 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
That's good Gerrit.
When you use CF you must be very careful which cell is active when you define it, as most of the formulae are relative, so can easily get offset by your current position. As I mentioned, the cell reference that you use when setting up the CF should be the active cell, either a single active cell, or the active cell in a selection. Oddly enough, I just re-did it, and on my first workbook it failed. Looking at the function list, it said there was a function Book1!Module1IsFormula. As I was in Book1, it should have just said IsFormula. But I haven't quite twigged why yet, but it failed in CF and in the worksheet. Creating a new workbook and doing it again, it worked fine. Odd! -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gerrit" wrote in message ... Bob you opened a new world for me, Thanks a million!! The UDF and the conditional Format works excellant on my computer (excell 2003).. For some strange reason I can't get it going on other computers also 2003... Went through the same drills (creating a UDF) but no luck... Gerrit "Bob Phillips" wrote: Yes you need the UDF as it is that that recognises a formula. Add the UDF to your workbook. Select your cells, I'm assuming that you start at A1 In CF change Condition 1 To Formula Is Add =IsFormula(A1) Set your format OK -- HTH Bob Phillips (remove nothere from email address if mailing direct) "mworth01" wrote in message ... I'm still a little confused as to how you then apply your UDF to a conditional format. The conditional format that I'm interested in applies only when a cell contains a formula; I'm not worried about whether it's a number or not. Do you still need a UDF since you only have one condition now? I'm new to conditional formatting, so thanks in advance for any help. -- mworth01 ------------------------------------------------------------------------ mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991 View this thread: http://www.excelforum.com/showthread...hreadid=512585 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set conditional format to show when a sell contain a formula
I have gotten around this in a less sophisticated way in the past. In the
formula box, I have put in a formula using a condition that evaluates to TRUE if the value of the cell equals the same result as the formula. This means you need to repeat the formula from the cell in CF. Here is a simple example I just wrote: =(SUM(B2:B4)<B5), where the formula in B5 is =sum(B2:B4). It highlights if the value is different from the answer the formula would give. If someone overwrites the formula with a value that is the same as the formula answer, it does not highlight, but I have not found this to be an issue in practice. I am not familiar with UDF's. I create big, complicated spreadsheets, and don't use Macros because of their speed, but complicated formulas can be cumbersome. Are UDF's fast? Where can I learn about them? If they are fast, the UDF solution is much more elegant than mine. "Bob Phillips" wrote: That's good Gerrit. When you use CF you must be very careful which cell is active when you define it, as most of the formulae are relative, so can easily get offset by your current position. As I mentioned, the cell reference that you use when setting up the CF should be the active cell, either a single active cell, or the active cell in a selection. Oddly enough, I just re-did it, and on my first workbook it failed. Looking at the function list, it said there was a function Book1!Module1IsFormula. As I was in Book1, it should have just said IsFormula. But I haven't quite twigged why yet, but it failed in CF and in the worksheet. Creating a new workbook and doing it again, it worked fine. Odd! -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gerrit" wrote in message ... Bob you opened a new world for me, Thanks a million!! The UDF and the conditional Format works excellant on my computer (excell 2003).. For some strange reason I can't get it going on other computers also 2003... Went through the same drills (creating a UDF) but no luck... Gerrit "Bob Phillips" wrote: Yes you need the UDF as it is that that recognises a formula. Add the UDF to your workbook. Select your cells, I'm assuming that you start at A1 In CF change Condition 1 To Formula Is Add =IsFormula(A1) Set your format OK -- HTH Bob Phillips (remove nothere from email address if mailing direct) "mworth01" wrote in message ... I'm still a little confused as to how you then apply your UDF to a conditional format. The conditional format that I'm interested in applies only when a cell contains a formula; I'm not worried about whether it's a number or not. Do you still need a UDF since you only have one condition now? I'm new to conditional formatting, so thanks in advance for any help. -- mworth01 ------------------------------------------------------------------------ mworth01's Profile: http://www.excelforum.com/member.php...fo&userid=8991 View this thread: http://www.excelforum.com/showthread...hreadid=512585 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
conditional formula to show "open" or "closed" | Excel Worksheet Functions | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions |