highlighting a change in value
I have a speadsheet with thousands of different values. Everytime someone
makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
Thanks very much that is great.
But can I change the formatting to something as specific as the fint red and bold? The code you sent me changes the cell pattern colour to purple. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
wllebelle, just change two lines in Bob's code, like this
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks very much that is great. But can I change the formatting to something as specific as the fint red and bold? The code you sent me changes the cell pattern colour to purple. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
thanks - this is much appreciated!
"Paul B" wrote: wllebelle, just change two lines in Bob's code, like this Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks very much that is great. But can I change the formatting to something as specific as the fint red and bold? The code you sent me changes the cell pattern colour to purple. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
Thanks - is there anyway to limit this to certain cells.
example: (F56:F300) and (G56:G300) "ellebelle" wrote: thanks - this is much appreciated! "Paul B" wrote: wllebelle, just change two lines in Bob's code, like this Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks very much that is great. But can I change the formatting to something as specific as the fint red and bold? The code you sent me changes the cell pattern colour to purple. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
ellebelle. try something like this,
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: If Intersect(Target, Range("F56:F300,G56:G300")) Then Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks - is there anyway to limit this to certain cells. example: (F56:F300) and (G56:G300) "ellebelle" wrote: thanks - this is much appreciated! "Paul B" wrote: wllebelle, just change two lines in Bob's code, like this Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks very much that is great. But can I change the formatting to something as specific as the fint red and bold? The code you sent me changes the cell pattern colour to purple. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
that worked - thanks!
"Paul B" wrote: ellebelle. try something like this, Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: If Intersect(Target, Range("F56:F300,G56:G300")) Then Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks - is there anyway to limit this to certain cells. example: (F56:F300) and (G56:G300) "ellebelle" wrote: thanks - this is much appreciated! "Paul B" wrote: wllebelle, just change two lines in Bob's code, like this Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Font.ColorIndex = 3 .Font.Bold = True End With ws_exit: Application.EnableEvents = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "ellebelle" wrote in message ... Thanks very much that is great. But can I change the formatting to something as specific as the fint red and bold? The code you sent me changes the cell pattern colour to purple. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target .Interior.ColorIndex = 38 End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips "ellebelle" wrote in message ... I have a speadsheet with thousands of different values. Everytime someone makes a change to a cell value I would like it to automatically change the formatting to highlight it. I would then reset the conditonal formatiing a t the beginning of each week. is there a way? |
All times are GMT +1. The time now is 12:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com