REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 On 10 Okt., 11:30, "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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 "Per Jessen" wrote in message ... 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 On 10 Okt., 11:30, "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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
Sorry forgot to dim the Isect
-- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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 "Per Jessen" wrote in message ... 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 On 10 Okt., 11:30, "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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 On 10 Okt., 14:16, "Ed Davis" wrote: 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"Per Jessen" wrote in message ... 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 On 10 Okt., 11:30, "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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
Got runtime error 1004
Method 'Intersect' of object ' _Global' failed -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... Sorry forgot to dim the Isect -- Thank You in Advance Ed Davis "Ed Davis" wrote in message ... 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 "Per Jessen" wrote in message ... 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 On 10 Okt., 11:30, "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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 "Ed Davis" wrote in message ... 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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
REPSOT?? Sub Worksheet_Change(ByVal Target As Range)
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 |
All times are GMT +1. The time now is 04:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com