REPSOT?? Sub Worksheet_Change(ByVal Target As Range)

[Ed Davis]

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.

 

[Per Jessen]

Hi Ed

Insert this in the codes sheet for ThisWorkbook, and it will fire on
changes in all sheets. If you have a sheet or two where you do not
want this feature, it can we can fix this too:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Set isect = Intersect(Range("G12:H51,P40:Q44,P48:Q60,Q14:Q17"),
Target)
If Not isect Is Nothing Then
Application.EnableEvents = False
Target.Interior.ColorIndex = 4
Application.EnableEvents = True
End If
End Sub

Regards,
Per

 

[Ed Davis]

Put this code in Thisworkbook module
Changed data on 2 sheets in cells G15 and H23 nothing happened.
No change in color.

--
Thank You in Advance
Ed Davis
Hi Ed

Insert this in the codes sheet for ThisWorkbook, and it will fire on
changes in all sheets. If you have a sheet or two where you do not
want this feature, it can we can fix this too:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Set isect = Intersect(Range("G12:H51,P40:Q44,P48:Q60,Q14:Q17"),
Target)
If Not isect Is Nothing Then
Application.EnableEvents = False
Target.Interior.ColorIndex = 4
Application.EnableEvents = True
End If
End Sub

Regards,
Per

 

[Ed Davis]

Sorry forgot to dim the Isect


--
Thank You in Advance
Ed Davis

Put this code in Thisworkbook module
Changed data on 2 sheets in cells G15 and H23 nothing happened.
No change in color.

--
Thank You in Advance
Ed Davis
Hi Ed

Insert this in the codes sheet for ThisWorkbook, and it will fire on
changes in all sheets. If you have a sheet or two where you do not
want this feature, it can we can fix this too:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Set isect = Intersect(Range("G12:H51,P40:Q44,P48:Q60,Q14:Q17"),
Target)
If Not isect Is Nothing Then
Application.EnableEvents = False
Target.Interior.ColorIndex = 4
Application.EnableEvents = True
End If
End Sub

Regards,
Per

 

[Per Jessen]

Fill color should change to green.

To ensure that events are enabled enter this in Immediate window and
hit enter:

Application.EnableEvents=True

Also you can insert a break point at the first line of the macro to
verify that the macro fires.

Regards,
Per

 

[Ed Davis]

Got runtime error 1004
Method 'Intersect' of object ' _Global' failed

--
Thank You in Advance
Ed Davis

Sorry forgot to dim the Isect

 

[Dave Peterson]

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]

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.

 

[Ed Davis]

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

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.

 

[Ed Davis]

I have been trying to add both procedures together so tried this.

Private Sub Workbook_SheetActivate(ByVal Sh As Object, ByVal Target as
Range)

I get a same name error.
Does anyone know how I can do both from the same procedure?


--
Thank You in Advance
Ed Davis

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.

 

[Dave Peterson]

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 fire:

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.

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.

 

[Ed Davis]

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

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 fire:

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.

 

[Dave Peterson]

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]

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]

I don't have any other suggestions.

Sorry.

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