Home 
Search 
Today's Posts 
#11




problem with udf
Post the UDF code and describe how you are using it in connection with CF.
Gord Dibben MS Excel MVP On Fri, 26 Sep 2008 06:29:20 0700, Bertus Swanepoel wrote: Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? 
#12




problem with udf
Hi,
The UDF code is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function The conditional formatting formula I use is: =isformula(A1) I then select the cells I want to apply this to in the applies to section. I have another conditional format to format all other text differently: =ISBLANK(A1) = FALSE So in sheet 1 I enter in E31: = 'Sheet2'!C28. when I hit enter E31 in Sheet 1 remains blank but in sheet2'!E32 a formula is entered saying Sheet1!D26. "Gord Dibben" wrote: Post the UDF code and describe how you are using it in connection with CF. Gord Dibben MS Excel MVP On Fri, 26 Sep 2008 06:29:20 0700, Bertus Swanepoel wrote: Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? 
#13




problem with udf
I see what you mean but only if I enter an = sign in sheet1 E31 then point
and click on sheet2 C28. If I enter =sheet2!C28 all works OK. I cannot explain why the point and click reacts as it does. Gord On Thu, 16 Oct 2008 03:09:00 0700, Bertus wrote: Hi, The UDF code is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function The conditional formatting formula I use is: =isformula(A1) I then select the cells I want to apply this to in the applies to section. I have another conditional format to format all other text differently: =ISBLANK(A1) = FALSE So in sheet 1 I enter in E31: = 'Sheet2'!C28. when I hit enter E31 in Sheet 1 remains blank but in sheet2'!E32 a formula is entered saying Sheet1!D26. "Gord Dibben" wrote: Post the UDF code and describe how you are using it in connection with CF. Gord Dibben MS Excel MVP On Fri, 26 Sep 2008 06:29:20 0700, Bertus Swanepoel wrote: Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? 
#14




Same problem
I have the same problem with this function  was a solution ever found?
Gord Dibben wrote: I see what you mean but only if I enter an = sign in sheet1 E31 then pointand 16Oct08 I see what you mean but only if I enter an = sign in sheet1 E31 then point and click on sheet2 C28. If I enter =sheet2!C28 all works OK. I cannot explain why the point and click reacts as it does. Gord On Thu, 16 Oct 2008 03:09:00 0700, Bertus wrote: Previous Posts In This Thread: On Wednesday, August 13, 2008 8:59 PM JeanMar wrote: Conditional format if cell contains formula Is there a way (conditional formatting?) to apply a different format to cells that contain a formula, as opposed to cells that have a directly entered value? On Thursday, August 14, 2008 12:09 AM YongHen wrote: sure. sure. here's an example A B C D E F G 1 no1 no2 no3 no4 no5 no6 2 ticket1 2 15 26 27 36 38 3 ticket2 1 12 13 15 24 34 4 ticket3 3 5 20 28 37 40 5 6 drawn 3 15 25 26 47 49 1. Select cells B2:G4 2. Select Conditional Formatting 3. Choose Formula Is (as opposed to Cell value is) 4. For the formula, use the CountIf function: =COUNTIF($B$6:$G$6,B2) 5. Click the Format button. 6. Select formatting options (choose a color for the cell), click OK What happens is that the conditional formatting formula will evaluate each cell to see if it should "count" according to the criteria. Once it is "counted", it will apply the format to the cell. Try it, its fun. Yong Heng "JeanMarc" wrote: On Thursday, August 14, 2008 12:39 AM JeanMar wrote: Thanks, but you misunderstood my question. Thanks, but you misunderstood my question. I am familiar with the operation of conditional formats. Here is a simple example of what I want to achieve: Cell A1 contains value '1' Cell A2 contains value '2' Cell A3 contains formula '=SUM(A1:A2)' Now what I would like is for cell A3 to be shown on a blue background, because it is a formula, whilst cells A1 and A2 should remain in the default format, because they are manually entered values. Basically is there an Excel function that is able to detect whether a cell is a formula or a single value? In other words, if the cell entry starts with '=', then I would like it to be formatted differently. Sounds easy, but I am stumped. Excel seems to only consider the *Value* in a cell, but not whether that value comes from a manual entry or a formula calculation. "Yong Heng" wrote: On Thursday, August 14, 2008 1:17 AM Peo Sjoblom wrote: You can use Excel 4 macro in a defined name formula to check if cell has a You can use Excel 4 macro in a defined name formula to check if cell has a formula, using you example do insertnamedefine, in the source box put =GET.CELL(6,Sheet1!$A1) and in the name box type in something descriptive like FormulaIs click OK Select A1:A3 with A1 as the active cell, do formatconditional formatting, select formula is and use =LEFT(FormulaIs)="=" or in a pedagogic manner =LEFT(FormulaIs,1)="=" you can actually leave out 1 if you just want one character now click the format button and select format and click OK twice  Regards, Peo Sjoblom "JeanMarc" wrote in message ... On Thursday, August 14, 2008 1:37 AM JeanMar wrote: Conditional format if cell contains formula Excellent  works great. Thanks. You are a guru. "Peo Sjoblom" wrote: On Thursday, August 14, 2008 1:59 AM T. Valko wrote: Conditional format if cell contains formula You can use a UDF (user defined function): Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function To use this: Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the project explorer pane on the left. Right click the file name Select: InsertModule Copy the code above and paste into the window on the right Return back to Excel: ALT Q Set the conditional formatting... Select the cell(s) in question. Assume this is cell A1. Goto FormatConditional Formatting Formula Is: =IsFormula(A1) Click the Format button Select the desired style(s) OK out  Biff Microsoft Excel MVP "JeanMarc" wrote in message ... On Thursday, August 14, 2008 2:17 AM JeanMar wrote: Conditional format if cell contains formula Thanks. Very elegant solution. "T. Valko" wrote: On Thursday, August 14, 2008 1:32 PM T. Valko wrote: You're welcome. Thanks for the feedback! You're welcome. Thanks for the feedback!  Biff Microsoft Excel MVP On Friday, September 26, 2008 9:29 AM Bertus Swanepoel wrote: problem with udf Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? On Friday, September 26, 2008 9:31 AM Bertus Swanepoel wrote: Conditional format if cell contains formula Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? I am working in Excel 2007. thanks On Thursday, October 16, 2008 6:09 AM Bertu wrote: problem with udf Hi, The UDF code is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function The conditional formatting formula I use is: =isformula(A1) I then select the cells I want to apply this to in the applies to section. I have another conditional format to format all other text differently: =ISBLANK(A1) = FALSE So in sheet 1 I enter in E31: = 'Sheet2'!C28. when I hit enter E31 in Sheet 1 remains blank but in sheet2'!E32 a formula is entered saying Sheet1!D26. "Gord Dibben" wrote: On Thursday, October 16, 2008 2:40 PM Gord Dibben wrote: I see what you mean but only if I enter an = sign in sheet1 E31 then pointand I see what you mean but only if I enter an = sign in sheet1 E31 then point and click on sheet2 C28. If I enter =sheet2!C28 all works OK. I cannot explain why the point and click reacts as it does. Gord On Thu, 16 Oct 2008 03:09:00 0700, Bertus wrote: On Sunday, June 07, 2009 6:26 PM Martin wrote: how to use this UDF to format that a formual is not present :) You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page. Submitted via EggHeadCafe  Software Developer Portal of Choice Adding WCF Service References http://www.eggheadcafe.com/tutorials...cerefere.aspx 
#15




You're welcome. Thanks for the feedback!
Just wanted to reinforce the feedback. Indeed very elegant...clarity of thought and code.
On Wednesday, August 13, 2008 8:59 PM JeanMar wrote: Is there a way (conditional formatting?) to apply a different format to cells that contain a formula, as opposed to cells that have a directly entered value? On Thursday, August 14, 2008 12:09 AM YongHen wrote: sure. here's an example A B C D E F G 1 no1 no2 no3 no4 no5 no6 2 ticket1 2 15 26 27 36 38 3 ticket2 1 12 13 15 24 34 4 ticket3 3 5 20 28 37 40 5 6 drawn 3 15 25 26 47 49 1. Select cells B2:G4 2. Select Conditional Formatting 3. Choose Formula Is (as opposed to Cell value is) 4. For the formula, use the CountIf function: =COUNTIF($B$6:$G$6,B2) 5. Click the Format button. 6. Select formatting options (choose a color for the cell), click OK What happens is that the conditional formatting formula will evaluate each cell to see if it should "count" according to the criteria. Once it is "counted", it will apply the format to the cell. Try it, its fun. Yong Heng "JeanMarc" wrote: On Thursday, August 14, 2008 12:39 AM JeanMar wrote: Thanks, but you misunderstood my question. I am familiar with the operation of conditional formats. Here is a simple example of what I want to achieve: Cell A1 contains value '1' Cell A2 contains value '2' Cell A3 contains formula '=SUM(A1:A2)' Now what I would like is for cell A3 to be shown on a blue background, because it is a formula, whilst cells A1 and A2 should remain in the default format, because they are manually entered values. Basically is there an Excel function that is able to detect whether a cell is a formula or a single value? In other words, if the cell entry starts with '=', then I would like it to be formatted differently. Sounds easy, but I am stumped. Excel seems to only consider the *Value* in a cell, but not whether that value comes from a manual entry or a formula calculation. "Yong Heng" wrote: On Thursday, August 14, 2008 1:17 AM Peo Sjoblom wrote: You can use Excel 4 macro in a defined name formula to check if cell has a formula, using you example do insertnamedefine, in the source box put =GET.CELL(6,Sheet1!$A1) and in the name box type in something descriptive like FormulaIs click OK Select A1:A3 with A1 as the active cell, do formatconditional formatting, select formula is and use =LEFT(FormulaIs)="=" or in a pedagogic manner =LEFT(FormulaIs,1)="=" you can actually leave out 1 if you just want one character now click the format button and select format and click OK twice  Regards, Peo Sjoblom "JeanMarc" wrote in message ... On Thursday, August 14, 2008 1:37 AM JeanMar wrote: Excellent  works great. Thanks. You are a guru. "Peo Sjoblom" wrote: On Thursday, August 14, 2008 1:59 AM T. Valko wrote: You can use a UDF (user defined function): Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function To use this: Open the VBE editor: ALT F11 Open the Project Explorer: CTRL R Locate your file name in the project explorer pane on the left. Right click the file name Select: InsertModule Copy the code above and paste into the window on the right Return back to Excel: ALT Q Set the conditional formatting... Select the cell(s) in question. Assume this is cell A1. Goto FormatConditional Formatting Formula Is: =IsFormula(A1) Click the Format button Select the desired style(s) OK out  Biff Microsoft Excel MVP "JeanMarc" wrote in message ... On Thursday, August 14, 2008 2:17 AM JeanMar wrote: Thanks. Very elegant solution. "T. Valko" wrote: On Thursday, August 14, 2008 1:32 PM T. Valko wrote: You're welcome. Thanks for the feedback!  Biff Microsoft Excel MVP On Friday, September 26, 2008 9:29 AM Bertus Swanepoel wrote: Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? On Friday, September 26, 2008 9:31 AM Bertus Swanepoel wrote: Hi, I have done this the UDF way, but now excel behaves strangely. I have applied the conditional formatting to cells in Sheet2. If I want to pick up a value from sheet 2 in sheet1 (formula in sheet 1 A1: ='Sheet2'!B6), nothing happend in sheet 1. Instead excel inputs a formula in a random cell in sheet 2 which refers to itself creating a circular reference. When I clear the conditional formatting from sheet 2 all is fine. Has anyone experienced this? I am working in Excel 2007. thanks On Thursday, October 16, 2008 6:09 AM Bertu wrote: Hi, The UDF code is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function The conditional formatting formula I use is: =isformula(A1) I then select the cells I want to apply this to in the applies to section. I have another conditional format to format all other text differently: =ISBLANK(A1) = FALSE So in sheet 1 I enter in E31: = 'Sheet2'!C28. when I hit enter E31 in Sheet 1 remains blank but in sheet2'!E32 a formula is entered saying Sheet1!D26. "Gord Dibben" wrote: On Thursday, October 16, 2008 2:40 PM Gord Dibben wrote: I see what you mean but only if I enter an = sign in sheet1 E31 then point and click on sheet2 C28. If I enter =sheet2!C28 all works OK. I cannot explain why the point and click reacts as it does. Gord On Thu, 16 Oct 2008 03:09:00 0700, Bertus wrote: On Sunday, June 07, 2009 6:26 PM Martin wrote: You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page. On Tuesday, March 16, 2010 5:07 PM Mike N wrote: I have the same problem with this function  was a solution ever found? On Saturday, October 16, 2010 2:42 AM monu 1123 wrote: How can we apply this in openoffice calc spreadsheets?? what modifications will be require? On Thursday, January 13, 2011 6:43 AM Martin Ruf wrote: Hi Biff, I used your suggestion and it worked perfectly! It was exactly what I wanted to do... But after I saved my file the conditional formatting function didn't work anymore... I have no idea why?!? So when I look in the creatded module thingy it still states your suggested formula: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function Also the conditional formatting formula is still there =IsFormula(M1) (M in my case) It is supposed to highlight cells with formula in red colour and values (no formulas) in white (actually in green since this is my background colour). Anyhow... everything is green after I saved the file. Any suggestions from your side? Thanks in advance and best regards, Martin On Thursday, January 13, 2011 8:17 AM Martin Ruf wrote: Nevermind... didn't activate macros. So problem solved :P 
#16




Conditional format if cell contains formula
On Thursday, 14 August 2008 07:59:01 UTC+7, JeanMarc wrote:
Is there a way (conditional formatting?) to apply a different format to cells that contain a formula, as opposed to cells that have a directly entered value? The opposite is possible to the same end. mark the cell range, whole sheet or sheets apply conditional formatting select "custom formular is" enter =isnumber(A1) select color This will color code all fields with numbers added with the designated color and leave all cells with formular or text in standard color. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How to make a conditional format recognize a formula in a cell  Excel Discussion (Misc queries)  
conditional format if the cell contains a formula  Excel Worksheet Functions  
Conditional format if a cell has a formula or if a percent or numb  Excel Discussion (Misc queries)  
Conditional Format  Formula to Colour Every 3rd Cell in Offset Range  Excel Discussion (Misc queries)  
How do I do conditional format based on a cell with a formula?  Excel Worksheet Functions 