I don't have any other suggestions.

Sorry.

Ed Davis wrote:

Hi Dave
Posted my issue on Ozgrib and this is what I got for a response:

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

"Dave Peterson" wrote in message
...
Why won't the code execute when you're stepping through it?

You can add a line to your code:

Stop

And the code will sit there waiting for you to hit F8 to step through the
rest
of it.

Ed Davis wrote:

Hi Dave
I tried several times to step through but they will not run in step
through.
I did find a way to CF the cells I want but whenever I put the CF in my
other macros will not run.
I put the CF in 1 sheet and tried to run my macros and they will not run
in
the sheet with that CF. In all other sheets the macros run without that
CF.

--
Thank You in Advance
Ed Davis
"Dave Peterson" wrote in message
...
I started a new workbook with two sheets.

I put these two procedures in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "hi from workbook_sheetactivate"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
MsgBox "hi from workbook_sheetchange"
End Sub

Each worked fine.

Maybe your code in one of the procedures is calling the other
procedure--have
you tried stepping through the code to follow the path.

If you find that this is true, you can tell excel to stop looking for
things
that would cause an event to fi

application.enableevents = false
sh.range("A1").value = "hi there"
application.enableevents = true

Assigning that value to A1 would usually fire the worksheet_sheetchange
event.
By turning off the events (.enableevents = false), that line of code
won't
fire
that (or any) event.

Ed Davis wrote:

I found out two things in the past 18hours.
Yes it took me about 18 hours to figure everything out. After a lot of
Internet searching and testing.

1st When using this Sub:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

I cannot also have this sub:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

2nd The Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target
As
Range)
will not work properly if the worksheet is protected even though the
cells
it is looking at are unlocked. I get an Object error.

I will have to try to incorporate the two subs somehow so that I can
accomplish both tasks.

Is it possible to use Private Sub Workbook_SheetChange(ByVal Sh As
Object,
ByVal Target As Range) as a regular sub routine and run it when the
user
is
done with the changes?
They have to run another macro when they are done changing anything
anyway.

The way I have things working now, if the user wants to change
something
they run a macro that, will "Unhide" some columns and rows, "Unlock"
the
cells they are allowed to change, Then it saves the active worksheet
as
a
temporary file. This macro also changes the TAB color to red. And
when
they
are done with changes they run another macro that saves the active
sheet
as
another temporary workbook so that I can compare the two to see what
the
changes were. Then it Hides the columns and rows and protects the
sheet
again. This is the macro where it should color the cells that were
changed
before the temporary save.

--
Thank You in Advance
Ed Davis
"Ed Davis" wrote in message
...
Put the code in ThisworkBook.
Added sheets to skip.
Changed colorindex to 3 as the cells are already 4.
Nothing happens at all.
Tried to step through but will not go just got beep.
Put Application.EnableEvents=True in immediate window
Still nothing happens.


--
Thank You in Advance
Ed Davis
"Dave Peterson" wrote in message
...
If there are lots of sheets that need this requirement, there are
probably a few
that don't (instructions or ????).

This is a workbook level event and goes in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target
As
Range)
Dim myCell As Range
Dim myIntersect As Range
Dim myAddresses As String
Dim SheetNamessToSkip As Variant
Dim res As Variant

SheetNamessToSkip = Array("Instructions", "Othersheetname")

myAddresses = "G12:H51,P40:Q44,P48:Q60,Q14:Q17"

res = Application.Match(Sh.Name, SheetNamessToSkip, 0)

If IsNumeric(res) Then
'it's in that array of names to skip
'do nothing
Else
Set myIntersect = Intersect(Sh.Range(myAddresses), Target)

If myIntersect Is Nothing Then
'do nothing
Else
For Each myCell In myIntersect.Cells
If myCell.HasFormula Then
'skip it, change the color back???
Else
myCell.Interior.ColorIndex = 4
End If
Next myCell
End If
End If
End Sub

I wasn't sure what should happen if someone puts the formula
back--you
may want
to remove that check or change the color????

Ed Davis wrote:

I have 4 rages of cells that if any cell in these ranges changes I
would
like to change the Background and Foreground colors.
However I only want to change that cells color not all of them.
Rather than use "Sub Worksheet_Change(ByVal Target As Range)" I
would
like
to use a macro because I have 32 sheets with these same ranges.
Currently when the user changes any of these cells a macro runs
that
saves
the active sheet as a workbook and changes the tab color to red to
indicate
a change has (possibly) been made.

I currently have no way of knowing what cell has been changed or
for
that
matter if any changes have been made at all.

The cell ranges are G12:H51, P40:Q44, P48:Q60 and Q14:Q17.
These cells have formulas in them and I allow the user to over
ride
with
a
number if it is incorrect.

I tried CF and have had no success.
A function was created to check if the cells had a formula. When
using
the
formula in CF it made a lot of other macros BOMB. Don't know the
reason
but
when that CF was removed all macros ran properly again.

I have seen a lot of information on using "Sub
Worksheet_Change(ByVal
Target
As Range)" but they all refer to 1 range and coloring the whole
range.

Any help with this would be greatly appreciated.

--
Thank You in Advance
Ed Davis

--

Dave Peterson



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson