Changing cell values

[Peter1973]

Is it possible to change cell values according to the last cell value eg:

I have a row of cell say A10:Z10 in these cells different letters are entered say either H, R or W is it possible that if I imput the letter S in a cell that every cell to the right of that S which contains a W will change to S, and vice versa ?

 

[HalfAce]

Hmm, well you can add this line to make it exit the sub when any numeric value is
entered within the range.
Paste this line in the Worksheet_Change routine:

If IsNumeric(Target.Value) Then Exit Sub 'work only on entry of alpha characters

inbetween these two lines:

If Target.Count > 1 Then Exit Sub 'work only on single cell changes
If Not Intersect(Target, Range("D10:BB256")) Is Nothing Then 'work only on changes in range D10:BB256

Does that sound like it'll work or will there be times when you actually want numeric
values to replace the alpha values?

 

[Peter1973]

Last snag I promise ! on my sheet it is sepaerated half way down on rows 70 to 75 by blank rows which have user data entered in ie dates comments etc. I have used this code for the whole sheet ie D10:BB256 which is great yet when I get below row 75 it seems to go strange ie any letter I type in is changed instead of just W & S.

Do I need to enter 2 seperate codes one for rows 10:70 then one for 75:256 or is it something I have done?

 

[Peter1973]

I have just noticed the new code seems to be changing all characters not just S & W on all rows ?

 

[HalfAce]

OK, as for working on all rows between 10 & 256, except rows 71:75, this change
should take care of that.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell selections
If Intersect(Target, Range("D10:BB70")) Is Nothing And _
   Intersect(Target, Range("D71:BB256")) Is Nothing Then Exit Sub 'work only on range D10:BB256
OldVal = Target.Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell changes
If IsNumeric(Target.Value) Then Exit Sub 'work only on entry of alpha characters
If Intersect(Target, Range("D10:BB70")) Is Nothing And _
   Intersect(Target, Range("D75:BB256")) Is Nothing Then Exit Sub
  Dim ThisCol As Integer, Col As Integer, ThisRow As Long
  ThisCol = Target.Column
  ThisRow = Target.Row
  If ThisCol < 54 Then
    For Col = ThisCol To 54
      If Cells(ThisRow, Col).Value = OldVal Then _
        Cells(ThisRow, Col).Value = Target.Value
    Next Col
  End If
End Sub

Now for the last post, I'm not sure I understand.
Starting in column D, I have the following. . . (one letter in each column.)
W H W W W W R H R R W R W (and so on)
I have this in rows 9 through 300
In any row between 10 & 256 (bseides 71:75) if I change (for example) the 'W' in
column G, then (just) all the Ws to the right of that in that row change to whatever
I replaced the W with.
Is this what you're after?
Is this not what you get as well?

 

[Peter1973]

You are right and the code works fine prehaps it's my fault but as you say in last post all the "w" to the right of that change to whatever I replaced the "w" with which is correct but it was probably my explaination that confused you, what I was trying to see if it was possible was that : refereance to your last post ,

If I change column G then all the ws to the right of that will change to whatever I replaced W with, I wanted that only to work on the letters "s" & "w". So the letters to the right of the cell will only change if I enter either s or w all other letters will not have any effect.

My fault I should have said.

 

[Peter1973]

Just read through my last post once again my explaination is lacking so here goes :

If i have a row a to bb with various letters in and I change one letter in that row, if the latter i eneter is either w or s then all the other ws or s's in that row will change to the last entry ie
a b c d e f g
w w r h w w
so if i changed column b from w to s then column e, f & g would also change to s and if then i chnged column e to w then f & g would change back to w. All other letters and times are static and if entered should have no effect on other cells.

 

[HalfAce]

Ohhh, Ok. I think I'm getting it now.
You want them to change only if the original value was an S or W and the new value is
the other of those two choices then?
If that's right then try this.

Option Explicit
Option Compare Text

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell selections
If Intersect(Target, Range("D10:BB70")) Is Nothing And _
   Intersect(Target, Range("D71:BB256")) Is Nothing Then Exit Sub 'work only on range D10:BB256
If Target.Value = "S" Or Target.Value = "W" Then OldVal = Target.Value Else OldVal = ""
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell changes
If IsNumeric(Target.Value) Then Exit Sub 'work only on entry of alpha characters
If Intersect(Target, Range("D10:BB70")) Is Nothing And _
   Intersect(Target, Range("D75:BB256")) Is Nothing Then Exit Sub
If Len(OldVal) = 0 Then Exit Sub

Dim ThisCol As Integer, Col As Integer, ThisRow As Long
ThisCol = Target.Column
ThisRow = Target.Row
If ThisCol < 54 Then
  For Col = ThisCol To 54
    Select Case Target.Value
      Case "S"
        If Cells(ThisRow, Col).Value = "W" Then _
           Cells(ThisRow, Col).Value = Target.Value
      Case "W"
        If Cells(ThisRow, Col).Value = "S" Then _
           Cells(ThisRow, Col).Value = Target.Value
    End Select
  Next Col
End If

End Sub

Am I understanding what you wanted now?

 

[Peter1973]

Just run a test page through and thats it.

Thanks for your patience.

 

[HalfAce]

Most welcome.
(And thanks for your patience.)

Let me know if it doesn't work when you get a chance to try it on the real thing.

 

[Peter1973]

I have just tried in on the real thing , I have altered my range to match my sheet which is H7:IA75 and H80:IA240 but when I enter my data now it works but only up to row BB should this happen ?