Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
Cell background has to be changed, if the cell doesn't contain formula.
I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
An unpleasant solution would be to purchase MS coditional format assistant
this adds the option of "contains" to the conditional formatting list (amongst others) so then you could change format based on if it contains an = sign as with a formula for instance. Its an unplesant solution cos it costs 24 bucks "Haritha Kolla" wrote: Cell background has to be changed, if the cell doesn't contain formula. I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
Thanks Atishoo for the reply. But, I cannot purchase MS Conditional format
assistant. "Atishoo" wrote: An unpleasant solution would be to purchase MS coditional format assistant this adds the option of "contains" to the conditional formatting list (amongst others) so then you could change format based on if it contains an = sign as with a formula for instance. Its an unplesant solution cos it costs 24 bucks "Haritha Kolla" wrote: Cell background has to be changed, if the cell doesn't contain formula. I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote:
Cell background has to be changed, if the cell doesn't contain formula. I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? Do you have to use Conditional formatting to do this? Not VBA? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
Salgud Thanks for the reply...
If user overwrites formula in the cell, the back ground should be changed to different color. But, if we implement this in VBA code or VSTO on cell change, the undo stack will be lost...and user will not be able to undo his changes. That is the reason why I wanted to handle it using Conditional Formatting.. "salgud" wrote: On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote: Cell background has to be changed, if the cell doesn't contain formula. I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? Do you have to use Conditional formatting to do this? Not VBA? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
What about saving the file under a modified name so the user can open the
saved file to replicate the undo? Of course, wouldn't work if you need the full stack. On Tue, 28 Jul 2009 20:53:58 -0700, Haritha Kolla wrote: Salgud Thanks for the reply... If user overwrites formula in the cell, the back ground should be changed to different color. But, if we implement this in VBA code or VSTO on cell change, the undo stack will be lost...and user will not be able to undo his changes. That is the reason why I wanted to handle it using Conditional Formatting.. "salgud" wrote: On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote: Cell background has to be changed, if the cell doesn't contain formula. I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? Do you have to use Conditional formatting to do this? Not VBA? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Conditional formatting if cell contains formula
I dont that would help.. I need the full undo stack
"salgud" wrote: What about saving the file under a modified name so the user can open the saved file to replicate the undo? Of course, wouldn't work if you need the full stack. On Tue, 28 Jul 2009 20:53:58 -0700, Haritha Kolla wrote: Salgud Thanks for the reply... If user overwrites formula in the cell, the back ground should be changed to different color. But, if we implement this in VBA code or VSTO on cell change, the undo stack will be lost...and user will not be able to undo his changes. That is the reason why I wanted to handle it using Conditional Formatting.. "salgud" wrote: On Tue, 28 Jul 2009 02:15:01 -0700, Haritha Kolla wrote: Cell background has to be changed, if the cell doesn't contain formula. I have tried implementing it in following ways I have tried changing the cell back ground using Conditional formatting with User Defined function(to check if cell has formula or value); but, adding User Defined function degrades the performance of the template. I have tried changing background by executing VSTO code on Range Change event. But, undo stack is lost and user is unable to undo the changes I also tried using Excel 4.0 macro..please see the implementation below.. 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 But, I cannot use this method, because I get a security warning for Excel 4.0 macros when I open the template. Is there any other way of checking if a cell contains formula in conditional formatting? Do you have to use Conditional formatting to do this? Not VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apply Conditional Formatting If Data in More Than 1 Cell Matches | Excel Discussion (Misc queries) | |||
Apply conditional formatting to individual cell Excel 2007 | Excel Worksheet Functions | |||
Conditional Formatting -- Apply to Row if Cell Value does not Matc | Excel Programming | |||
apply conditional formatting to negative cell values | Excel Discussion (Misc queries) | |||
How does special formula apply to conditional formatting? | Excel Discussion (Misc queries) |