Conditional Format

[Ed Davis]

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.

 

[JLatham]

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]

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.

 

[Gord Dibben]

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

Format>CF>Formula 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

 

[Ed Davis]

Works like a charm.
I never get tired of suggestions. That is the way we learn.

Thanks Gord

 

[Ed Davis]

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
Format>CF>Formula 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 are:
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.

 

[Gord Dibben]

It is not the UDF and CF that is a problem.

You have something else that is disrupting your macros.


Gord

 

[Ed Davis]

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

 

[Ed Davis]

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.

 

[Ed Davis]

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