Conditional Formatting - to highlight overwritten formulas
in [Functions]
|
Prev: Help with data points in line graph in Chart function in Excel.
Next: changing worksheet reference name when copying down cells
From: JohnG on 18 May 2010 21:24 A number of people can access and update a spreadsheet that I designed for them. The sheet contains a column with lookup formulas. I need to be able to quickly scan down the column and see cells where the formulas have been overwritten with values/text. Is there a way to set this up with conditional formatting. My first thought was to use the TYPE function but it does not appear to be able to identify when the contents of a cell is derived from a formula as opposed to a simple text string or number. -- JohnG
From: T. Valko on 18 May 2010 21:35 See this: http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/6bf323d2cf859ecd?tvc=2 -- Biff Microsoft Excel MVP "JohnG" <JohnG(a)discussions.microsoft.com> wrote in message news:CC792E38-6D53-4303-A32E-967ACEF8A12F(a)microsoft.com... >A number of people can access and update a spreadsheet that I designed for > them. The sheet contains a column with lookup formulas. I need to be able > to > quickly scan down the column and see cells where the formulas have been > overwritten with values/text. Is there a way to set this up with > conditional > formatting. > My first thought was to use the TYPE function but it does not appear to be > able to identify when the contents of a cell is derived from a formula as > opposed to a simple text string or number. > -- > JohnG
From: Max on 18 May 2010 22:08 Perhaps a simple, fast alternative ... Select the col/range, then press F5 > Special > check Constants > OK. This selects all the non-formula cells within the col/range in a flash, then just click to format at one go with the desired fill color. Any worth? hit the YES below -- Max Singapore --- "JohnG" wrote: > A number of people can access and update a spreadsheet that I designed for > them. The sheet contains a column with lookup formulas. I need to be able to > quickly scan down the column and see cells where the formulas have been > overwritten with values/text. Is there a way to set this up with conditional > formatting. > My first thought was to use the TYPE function but it does not appear to be > able to identify when the contents of a cell is derived from a formula as > opposed to a simple text string or number. > -- > JohnG
From: Tom Hutchins on 18 May 2010 22:18 Here is one way. Add this little user-defined function to your workbook: Public Function HasRx(MyCell As Range) As Variant HasRx = MyCell.HasFormula End Function Select all of column A. Then add conditional formatting based on the following formula: =AND(LEN(A1)>0,HasRx(A1)=FALSE) Put the function code in a general VBA module in your workbook. If you are new to user-defined functions (macros), this link to Jon Peltier's site may be helpful: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/ Hope this helps, Hutch "JohnG" wrote: > A number of people can access and update a spreadsheet that I designed for > them. The sheet contains a column with lookup formulas. I need to be able to > quickly scan down the column and see cells where the formulas have been > overwritten with values/text. Is there a way to set this up with conditional > formatting. > My first thought was to use the TYPE function but it does not appear to be > able to identify when the contents of a cell is derived from a formula as > opposed to a simple text string or number. > -- > JohnG
From: JohnG on 18 May 2010 22:26
Thanks Max - I am looking for an automatic solution as I also want the less experienced contributors to the spreadsheet to realise that they have overwritten the default formula. I also prefer not to use VBA or macros in the solution as someone less experienced will need to maintain the spreadsheet when I move on -- JohnG "Max" wrote: > Perhaps a simple, fast alternative ... Select the col/range, then press F5 > > Special > check Constants > OK. This selects all the non-formula cells within > the col/range in a flash, then just click to format at one go with the > desired fill color. Any worth? hit the YES below > -- > Max > Singapore > --- > "JohnG" wrote: > > A number of people can access and update a spreadsheet that I designed for > > them. The sheet contains a column with lookup formulas. I need to be able to > > quickly scan down the column and see cells where the formulas have been > > overwritten with values/text. Is there a way to set this up with conditional > > formatting. > > My first thought was to use the TYPE function but it does not appear to be > > able to identify when the contents of a cell is derived from a formula as > > opposed to a simple text string or number. > > -- > > JohnG |