Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
Is there a way to conditional format a cell that does not have a formula.
It does not matter what the formula is as long as it does not have one. I allow users to overwrite formulas but would like to be able to see what ones were over written with a number. I tried using ISNUMBER but even if it is a formula it returns TRUE. -- Thank You in Advance Ed Davis |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
Conditional formatting is dependent on either a formula or value in a cell.
But in the 'mind' of Excel, a value is a formula. The code equivalent of Edit -- Paste Special -- Values goes something like this (where A1 contains a formula initially) Range("A1").Formula = Range("A1").Value Your conditional format statement might possibly work if you can limit the range, but I suspect that your users may be typing in values that are very much like the results of the formula. You could probably do this with VBA code associated with the Worksheet_Change() event. "Ed Davis" wrote: Is there a way to conditional format a cell that does not have a formula. It does not matter what the formula is as long as it does not have one. I allow users to overwrite formulas but would like to be able to see what ones were over written with a number. I tried using ISNUMBER but even if it is a formula it returns TRUE. -- Thank You in Advance Ed Davis |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
All the formulas that they would be allowed to change are links to the
previous page. However out of 900 formulas on a sheet they are only allowed to change about 100. There are 32 sheets with the same situation. So I do not think a worksheet_change()event would not work as there would be so many changes and the cells they are allowed to change would only be done once in a while, maybe about 5 times a month or 5 sheets a month. Currently what I am doing to track the changes is saving the sheet they change and turning the tab red. Then I can view the sheet they changed but have to compare in order to find the changes. -- Thank You in Advance Ed Davis "JLatham" wrote in message ... Conditional formatting is dependent on either a formula or value in a cell. But in the 'mind' of Excel, a value is a formula. The code equivalent of Edit -- Paste Special -- Values goes something like this (where A1 contains a formula initially) Range("A1").Formula = Range("A1").Value Your conditional format statement might possibly work if you can limit the range, but I suspect that your users may be typing in values that are very much like the results of the formula. You could probably do this with VBA code associated with the Worksheet_Change() event. "Ed Davis" wrote: Is there a way to conditional format a cell that does not have a formula. It does not matter what the formula is as long as it does not have one. I allow users to overwrite formulas but would like to be able to see what ones were over written with a number. I tried using ISNUMBER but even if it is a formula it returns TRUE. -- Thank You in Advance Ed Davis |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
You may be tired of my suggestions by now Ed<g
Copy this UDF to a general module. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. When a formula is overwritten with a number, the cell will change to the chosen color. Gord Dibben MS Excel MVP On Wed, 7 Oct 2009 07:42:30 -0300, "Ed Davis" wrote: Is there a way to conditional format a cell that does not have a formula. It does not matter what the formula is as long as it does not have one. I allow users to overwrite formulas but would like to be able to see what ones were over written with a number. I tried using ISNUMBER but even if it is a formula it returns TRUE. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
Works like a charm.
I never get tired of suggestions. That is the way we learn. Thanks Gord -- Thank You in Advance Ed Davis "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You may be tired of my suggestions by now Ed<g Copy this UDF to a general module. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. When a formula is overwritten with a number, the cell will change to the chosen color. Gord Dibben MS Excel MVP On Wed, 7 Oct 2009 07:42:30 -0300, "Ed Davis" wrote: Is there a way to conditional format a cell that does not have a formula. It does not matter what the formula is as long as it does not have one. I allow users to overwrite formulas but would like to be able to see what ones were over written with a number. I tried using ISNUMBER but even if it is a formula it returns TRUE. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
Hi Gord
This function that you wrote for me works just the way it should. In most cases it would be perfect. I have added it to my library of Sub Routines and Functions. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function And then in conditional format FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. I have found however, that I can not run a macro on the worksheets that have the conditional formatting using this function. I have tried several macros after using it and some work properly and some do not. I have tried macros that do the following : These macros do not work. 1. Select all Visible sheets 2. Unhide Columns and or Rows. 3. Hide Columns and or Rows Macros that have worked with it a 1. Copy worksheets. 2. Copy and Paste Values. 3. Copy and Paste Formats. 4. Copy and Paste Formulas. 5. All Workbook and Worksheet Save functions. 6. Unprotect all Worksheets. 7. Protect all Worksheets. It appears that any macro that uses the word "HIDDEN" or "VISIBLE" do not work. I do want you to know I appreciate all the hard work you have put into this and many other topics that you have helped me with. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Works like a charm. I never get tired of suggestions. That is the way we learn. Thanks Gord -- Thank You in Advance Ed Davis "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You may be tired of my suggestions by now Ed<g Copy this UDF to a general module. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. When a formula is overwritten with a number, the cell will change to the chosen color. Gord Dibben MS Excel MVP On Wed, 7 Oct 2009 07:42:30 -0300, "Ed Davis" wrote: Is there a way to conditional format a cell that does not have a formula. It does not matter what the formula is as long as it does not have one. I allow users to overwrite formulas but would like to be able to see what ones were over written with a number. I tried using ISNUMBER but even if it is a formula it returns TRUE. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
It is not the UDF and CF that is a problem.
You have something else that is disrupting your macros. Gord On Thu, 8 Oct 2009 07:39:48 -0300, "Ed Davis" wrote: Hi Gord This function that you wrote for me works just the way it should. In most cases it would be perfect. I have added it to my library of Sub Routines and Functions. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function And then in conditional format FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. I have found however, that I can not run a macro on the worksheets that have the conditional formatting using this function. I have tried several macros after using it and some work properly and some do not. I have tried macros that do the following : These macros do not work. 1. Select all Visible sheets 2. Unhide Columns and or Rows. 3. Hide Columns and or Rows Macros that have worked with it a 1. Copy worksheets. 2. Copy and Paste Values. 3. Copy and Paste Formats. 4. Copy and Paste Formulas. 5. All Workbook and Worksheet Save functions. 6. Unprotect all Worksheets. 7. Protect all Worksheets. It appears that any macro that uses the word "HIDDEN" or "VISIBLE" do not work. I do want you to know I appreciate all the hard work you have put into this and many other topics that you have helped me with. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
I have taken a lot out of my macro that hides rows and columns to this:
Using the watch for Rows and columns. The macro gets to the rows. But does not hide them and then the macro stops and does nothing. When I remove the CF the macro runs the way it should. Sub Done() ' ' Done Macro ' Rows("64:200").Hidden = False Columns("B:F").Hidden = False Columns("G:I").Hidden = True Columns("J:O").Hidden = False Columns("P:S").Hidden = True ActiveSheet.Protect Password:="7135" Range("A1").Select End Sub -- Thank You in Advance Ed Davis "Gord Dibben" <gorddibbATshawDOTca wrote in message ... It is not the UDF and CF that is a problem. You have something else that is disrupting your macros. Gord On Thu, 8 Oct 2009 07:39:48 -0300, "Ed Davis" wrote: Hi Gord This function that you wrote for me works just the way it should. In most cases it would be perfect. I have added it to my library of Sub Routines and Functions. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function And then in conditional format FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. I have found however, that I can not run a macro on the worksheets that have the conditional formatting using this function. I have tried several macros after using it and some work properly and some do not. I have tried macros that do the following : These macros do not work. 1. Select all Visible sheets 2. Unhide Columns and or Rows. 3. Hide Columns and or Rows Macros that have worked with it a 1. Copy worksheets. 2. Copy and Paste Values. 3. Copy and Paste Formats. 4. Copy and Paste Formulas. 5. All Workbook and Worksheet Save functions. 6. Unprotect all Worksheets. 7. Protect all Worksheets. It appears that any macro that uses the word "HIDDEN" or "VISIBLE" do not work. I do want you to know I appreciate all the hard work you have put into this and many other topics that you have helped me with. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
After removing the CF regarding the new function all macros are working
properly again. All other CF is still the same. So it appears it had to be the new function CF. -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... I have taken a lot out of my macro that hides rows and columns to this: Using the watch for Rows and columns. The macro gets to the rows. But does not hide them and then the macro stops and does nothing. When I remove the CF the macro runs the way it should. Sub Done() ' ' Done Macro ' Rows("64:200").Hidden = False Columns("B:F").Hidden = False Columns("G:I").Hidden = True Columns("J:O").Hidden = False Columns("P:S").Hidden = True ActiveSheet.Protect Password:="7135" Range("A1").Select End Sub -- Thank You in Advance Ed Davis "Gord Dibben" <gorddibbATshawDOTca wrote in message ... It is not the UDF and CF that is a problem. You have something else that is disrupting your macros. Gord On Thu, 8 Oct 2009 07:39:48 -0300, "Ed Davis" wrote: Hi Gord This function that you wrote for me works just the way it should. In most cases it would be perfect. I have added it to my library of Sub Routines and Functions. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function And then in conditional format FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. I have found however, that I can not run a macro on the worksheets that have the conditional formatting using this function. I have tried several macros after using it and some work properly and some do not. I have tried macros that do the following : These macros do not work. 1. Select all Visible sheets 2. Unhide Columns and or Rows. 3. Hide Columns and or Rows Macros that have worked with it a 1. Copy worksheets. 2. Copy and Paste Values. 3. Copy and Paste Formats. 4. Copy and Paste Formulas. 5. All Workbook and Worksheet Save functions. 6. Unprotect all Worksheets. 7. Protect all Worksheets. It appears that any macro that uses the word "HIDDEN" or "VISIBLE" do not work. I do want you to know I appreciate all the hard work you have put into this and many other topics that you have helped me with. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
Hi Gord
I put my issue on OZgrid and this was the reply I got: REPLY: Unless its a limitation of conditional formatting, I do believe you found a bug. I reproduced this as you described. If I record a macro to unhide the rows... the rows unhide fine. Produces the following code Cells.EntireRow.Hidden = False 'this will unhide all rows and columns. However, if I run that macro afterwards, it fails on that line - no error. Just stops running. This does not appear to be related to locked cells/protected sheets. When I removed the conditional formatting, the above code worked fine. In fact, I changed the conditional formatting to look like =LEN(E7)=0 And this actually allowed the cells.entirerow.hidden to run just fine. However, when the CF was referencing a UDF function, it failed everytime. Not sure why... thats why I think its bug. This was on Excel 2007. Thanks, Ger END OF REPLY "Gord Dibben" <gorddibbATshawDOTca wrote in message ... It is not the UDF and CF that is a problem. You have something else that is disrupting your macros. Gord On Thu, 8 Oct 2009 07:39:48 -0300, "Ed Davis" wrote: Hi Gord This function that you wrote for me works just the way it should. In most cases it would be perfect. I have added it to my library of Sub Routines and Functions. Function IsFormula(Cell) IsFormula = Cell.HasFormula End Function And then in conditional format FormatCFFormula is: =NOT(IsFormula(A1)) Format to a color. I have found however, that I can not run a macro on the worksheets that have the conditional formatting using this function. I have tried several macros after using it and some work properly and some do not. I have tried macros that do the following : These macros do not work. 1. Select all Visible sheets 2. Unhide Columns and or Rows. 3. Hide Columns and or Rows Macros that have worked with it a 1. Copy worksheets. 2. Copy and Paste Values. 3. Copy and Paste Formats. 4. Copy and Paste Formulas. 5. All Workbook and Worksheet Save functions. 6. Unprotect all Worksheets. 7. Protect all Worksheets. It appears that any macro that uses the word "HIDDEN" or "VISIBLE" do not work. I do want you to know I appreciate all the hard work you have put into this and many other topics that you have helped me with. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capture conditional format as cell format | Excel Discussion (Misc queries) | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |