VBA Code help

[robert morris]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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]

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\)]

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]

Rick,

JUST BEAUTIFUL! Thanks so much. I'm just learning and at age 77 so little
time and so much to learn. Thanks again.

Bob M.