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

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

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

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.

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.

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.

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.

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.

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.

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.