Changing Range of Cell Value Based on Anothr Column Cell

[madhuchelliah]

Hello Mates, I have a requirement to change values of range of cells based on another column. It is very very complicated one. Go through the example below. If any of the cell in I column contains EXPIRED word then the below value of corresponding cell value(E3) in E column should change to one level up. It should change value up to next same value of the cell(E2). If the E2 cell contains value 3 then the below values should change one level up, up to next 3 value in E column. Last condition the macros should work if the value 2.....n only. It should not work if the E column have 1 value

INPUT OUTPUT

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD="align: center"]E
[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD="align: center"]E
[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]EXPIRED[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[NdNoviceHlp]

The cell may contain other words than "EXPIRED"?????
Your words.....
"1. It should stop when it finds Expired word
2. It should stop before the same value
3. It should stop if the value is less than the value of expired cell"
The output does what was requested.
This code will address your new found "other words". It's impossible to code for continually changing unknown needs. I'm done. Good luck. Dave

Option Explicit
Sub Test()
Dim Lastrow As Integer, Cnt As Integer, Cnt2 As Integer, Temp As Integer
With Sheets("Sheet1")
    Lastrow = .Range("E" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To Lastrow
If Sheets("Sheet1").Range("E" & Cnt).Value <> 1 Then
If InStr(Sheets("Sheet1").Range("I" & Cnt).Value, "EXPIRED") Then
Temp = Sheets("Sheet1").Range("E" & Cnt).Value
Cnt2 = Cnt + 1
Do Until InStr(Sheets("Sheet1").Range("I" & Cnt2).Value, "EXPIRED")
If Cnt2 > Lastrow Then
Exit Sub
End If
If (Sheets("Sheet1").Range("E" & Cnt2).Value <> Temp) And _
        (Sheets("Sheet1").Range("E" & Cnt2).Value > Temp) Then
Sheets("Sheet1").Range("E" & Cnt2).Value = _
                 Sheets("Sheet1").Range("E" & Cnt2).Value - 1
End If
Cnt2 = Cnt2 + 1
Loop
Cnt = Cnt2 - 1
End If
End If
Next Cnt
End Sub