Format cells without a formula
Hi
I have a large spreadsheet with formulae in multiple cells. Occassionally under some circumstances I have to replace a formula with manually entered data and I need to be able to automatically highlight these cells as being different. I have found out how to conditionally format a cell containing a formula but can't make the adjsutment to format a cell that doesn't contain a formula. The code I found is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function Is it as simple as changing "HasFormula" to "doesnothaveformula"? Thanks Martin |
Format cells without a formula
The use of NOT sounds more likely.
You can either use =NOT(IsFormula(cellref)) as your CF condition, or change your function to something like: Function IsNotFormula(cell_ref As Range) IsNotFormula = Not (cell_ref.HasFormula) End Function -- David Biddulph wrote: Hi I have a large spreadsheet with formulae in multiple cells. Occassionally under some circumstances I have to replace a formula with manually entered data and I need to be able to automatically highlight these cells as being different. I have found out how to conditionally format a cell containing a formula but can't make the adjsutment to format a cell that doesn't contain a formula. The code I found is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function Is it as simple as changing "HasFormula" to "doesnothaveformula"? Thanks Martin |
Format cells without a formula
Hi,
You may try this. 1. Press Ctrl+F3 to get the Define Name box; 2. Type in a name there, say cellhasformula 3. In the Refers to box, type =GET.CELL(48,INDIRECT("rc",FALSE)) 4. Now go to the first number of the range and in the "Formula Is" drop down box, type the following formula =cellhasformula=FALSE 5. Select the desired formatting 6. Now copy down the conditional formatting You will now see all the hard coded numbers in the desired formatting. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com " wrote in message ... Hi I have a large spreadsheet with formulae in multiple cells. Occassionally under some circumstances I have to replace a formula with manually entered data and I need to be able to automatically highlight these cells as being different. I have found out how to conditionally format a cell containing a formula but can't make the adjsutment to format a cell that doesn't contain a formula. The code I found is: Function IsFormula(cell_ref As Range) IsFormula = cell_ref.HasFormula End Function Is it as simple as changing "HasFormula" to "doesnothaveformula"? Thanks Martin |
Format cells without a formula
Thanks, that's done the trick nicely
Martin |
All times are GMT +1. The time now is 05:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com