Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code help
Could someone help me with an event macro? I would like to have the last number in a cell I change in each row change color. Range is F4:Y75. If I change a number in H24, I would like it to change color. Once a change has been entered in H24, if I then change a number in M30 it should change color but H24 would remain as changed. Confused? So am I. Bob M. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code help
I think you are going to need VBA code to handle this. Right-click on the
worksheet tab for the sheet where you want this functionality to occur and select View Code from the popup menu that appears. Then copy/paste the following into the code window that appeared when you chose to View Code... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Target.Cells.Interior.Color = vbRed End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Now, go back to the sheet and change some cells in the range you specified (also try typing in exactly what was in the cell when you entered it)... does this do what you wanted? Rick "robert morris" wrote in message ... Could someone help me with an event macro? I would like to have the last number in a cell I change in each row change color. Range is F4:Y75. If I change a number in H24, I would like it to change color. Once a change has been entered in H24, if I then change a number in M30 it should change color but H24 would remain as changed. Confused? So am I. Bob M. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code help
Rick,
The code works beautifully but for one thing. Once a change has been made in a cell, the previous change in that row needs to return to normal (blk/white) I only want one change of color per row. Possible? Many thanks Bob M. "Rick Rothstein (MVP - VB)" wrote: I think you are going to need VBA code to handle this. Right-click on the worksheet tab for the sheet where you want this functionality to occur and select View Code from the popup menu that appears. Then copy/paste the following into the code window that appeared when you chose to View Code... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Target.Cells.Interior.Color = vbRed End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Now, go back to the sheet and change some cells in the range you specified (also try typing in exactly what was in the cell when you entered it)... does this do what you wanted? Rick "robert morris" wrote in message ... Could someone help me with an event macro? I would like to have the last number in a cell I change in each row change color. Range is F4:Y75. If I change a number in H24, I would like it to change color. Once a change has been entered in H24, if I then change a number in M30 it should change color but H24 would remain as changed. Confused? So am I. Bob M. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code help
Sorry, I missed the "one color change per row" requirement. Try this code
instead... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Range("F" & Target.Row & ":Y" & Target.Row).Cells. _ Interior.ColorIndex = xlColorIndexNone Target.Cells.Interior.Color = vbRed End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Rick "robert morris" wrote in message ... Rick, The code works beautifully but for one thing. Once a change has been made in a cell, the previous change in that row needs to return to normal (blk/white) I only want one change of color per row. Possible? Many thanks Bob M. "Rick Rothstein (MVP - VB)" wrote: I think you are going to need VBA code to handle this. Right-click on the worksheet tab for the sheet where you want this functionality to occur and select View Code from the popup menu that appears. Then copy/paste the following into the code window that appeared when you chose to View Code... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Target.Cells.Interior.Color = vbRed End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Now, go back to the sheet and change some cells in the range you specified (also try typing in exactly what was in the cell when you entered it)... does this do what you wanted? Rick "robert morris" wrote in message ... Could someone help me with an event macro? I would like to have the last number in a cell I change in each row change color. Range is F4:Y75. If I change a number in H24, I would like it to change color. Once a change has been entered in H24, if I then change a number in M30 it should change color but H24 would remain as changed. Confused? So am I. Bob M. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code help
Rick,
Per your suggestion, I tried using the same number in a cell and it did not add the color, which is a bad thing as some of the changes could be the same number. Other than that, it works beautifully. I don't understand why it would not add the color using the same number. Bob. M. "Rick Rothstein (MVP - VB)" wrote: Sorry, I missed the "one color change per row" requirement. Try this code instead... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Range("F" & Target.Row & ":Y" & Target.Row).Cells. _ Interior.ColorIndex = xlColorIndexNone Target.Cells.Interior.Color = vbRed End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Rick "robert morris" wrote in message ... Rick, The code works beautifully but for one thing. Once a change has been made in a cell, the previous change in that row needs to return to normal (blk/white) I only want one change of color per row. Possible? Many thanks Bob M. "Rick Rothstein (MVP - VB)" wrote: I think you are going to need VBA code to handle this. Right-click on the worksheet tab for the sheet where you want this functionality to occur and select View Code from the popup menu that appears. Then copy/paste the following into the code window that appeared when you chose to View Code... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Target.Cells.Interior.Color = vbRed End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Now, go back to the sheet and change some cells in the range you specified (also try typing in exactly what was in the cell when you entered it)... does this do what you wanted? Rick "robert morris" wrote in message ... Could someone help me with an event macro? I would like to have the last number in a cell I change in each row change color. Range is F4:Y75. If I change a number in H24, I would like it to change color. Once a change has been entered in H24, if I then change a number in M30 it should change color but H24 would remain as changed. Confused? So am I. Bob M. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code help
It doesn't change colors under that condition because I designed it that
way. Why? Because you wrote this in your initial post... "If I change a number in H24, I would like it to change color" I read that as meaning if you don't change the number in H24, then the color shouldn't change (typing the same thing into a cell that was already there is not really a change). Anyway, give this code a try (notice that I am eliminating both the Dim'ming of InValue and the SelectionChange event procedure (they were only needed to track whether the contents of a cell were different or not)... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then Range("F" & Target.Row & ":Y" & Target.Row).Cells. _ Interior.ColorIndex = xlColorIndexNone Target.Cells.Interior.Color = vbRed End If End Sub Rick "robert morris" wrote in message ... Rick, Per your suggestion, I tried using the same number in a cell and it did not add the color, which is a bad thing as some of the changes could be the same number. Other than that, it works beautifully. I don't understand why it would not add the color using the same number. Bob. M. "Rick Rothstein (MVP - VB)" wrote: Sorry, I missed the "one color change per row" requirement. Try this code instead... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Range("F" & Target.Row & ":Y" & Target.Row).Cells. _ Interior.ColorIndex = xlColorIndexNone Target.Cells.Interior.Color = vbRed End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Rick "robert morris" wrote in message ... Rick, The code works beautifully but for one thing. Once a change has been made in a cell, the previous change in that row needs to return to normal (blk/white) I only want one change of color per row. Possible? Many thanks Bob M. "Rick Rothstein (MVP - VB)" wrote: I think you are going to need VBA code to handle this. Right-click on the worksheet tab for the sheet where you want this functionality to occur and select View Code from the popup menu that appears. Then copy/paste the following into the code window that appeared when you chose to View Code... Dim InValue As String Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F4:Y75")) Is Nothing Then If Target.Value < InValue Then Target.Cells.Interior.Color = vbRed End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) InValue = Target.Value End Sub Now, go back to the sheet and change some cells in the range you specified (also try typing in exactly what was in the cell when you entered it)... does this do what you wanted? Rick "robert morris" wrote in message ... Could someone help me with an event macro? I would like to have the last number in a cell I change in each row change color. Range is F4:Y75. If I change a number in H24, I would like it to change color. Once a change has been entered in H24, if I then change a number in M30 it should change color but H24 would remain as changed. Confused? So am I. Bob M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Code expantion , with code! | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |