Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change FORMAT | Excel Discussion (Misc queries) | |||
Cell Change Color - Need Help | New Users to Excel | |||
conditional cell shading when a change occurs | Excel Discussion (Misc queries) | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) | |||
highlighting positive or negative change | Excel Discussion (Misc queries) |