Changing Range of Cell Value Based on Anothr Column Cell

M

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
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]
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
bump...try again to explain your needs. Have someone unfamiliar with what U want to achieve read it over and see if they understand. I'm guessing the lack of replies is an indicator that potential helpers are as confused as I am. HTH. Dave
 
Upvote 0
Thanks for your feedback. I try to explain ot again. If a cell(I5) in I column contains EXPIRED word then the below values of corresponding cell value(E5) in E column should change to one numeric value down like 3to2 4to3 5to4. It should change value up to next same value of the cell(E4) that is 2 which is in E10. The first table shows the scenerio, the second table shows with bold is my requirment. One correction in second table, the word EXPIRED shown in I15 should be in I14. The cells mentioned are for reference purpose only. The word EXPIRED will be in any of the cell in I column. I hope this explanation understands clearly.
 
Last edited:
Upvote 0
Do U want a ws formula solution or VBA... VBA would be rather simple? I'm guessing that there's some clever formula that could be placed in "E:E" so that the "E" values change based on the content of both "I" and the rows above the active cell in "E". Does that sound about right? Dave
 
Upvote 0
Sheet1 code. Seems to produce the output U want. Dave
Code: 
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 Sheets("Sheet1").Range("I" & Cnt).Value = "EXPIRED" Then
Temp = Sheets("Sheet1").Range("E" & Cnt).Value
Cnt2 = Cnt + 1
Do Until Sheets("Sheet1").Range("E" & Cnt2).Value = Temp
Sheets("Sheet1").Range("E" & Cnt2).Value = _
                 Sheets("Sheet1").Range("E" & Cnt2).Value - 1
Cnt2 = Cnt2 + 1
Loop
End If
End If
Next Cnt
End Sub
 
Upvote 0
Hi dude, its working great!!!!!!!!!!! But i got issue with one scenario. please check the table below. First table is the scenario second table is the result i got with the code third table should be the expected result. I give you the explanation. As per the code it should change up to next 2 value that's why it changed all cells to 2. Since E5 value has EXPIRED word the code should stop there and change the values below to 3. Hope you can understand this. Please try to solve this. Thank you.

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD="align: center"]E
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]EXPIRED
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]EXPIRED
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]EXPIRED
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]EXPIRED
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]EXPIRED
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]EXPIRED
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Untested but U should just be able to do this...
Code: 
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 Sheets("Sheet1").Range("I" & Cnt).Value = "EXPIRED" Then
Temp = Sheets("Sheet1").Range("E" & Cnt).Value
Cnt2 = Cnt + 1
Do Until (Sheets("Sheet1").Range("E" & Cnt2).Value = Temp) Or _
Sheets("Sheet1").Range("I" & Cnt2).Value = "EXPIRED"
Sheets("Sheet1").Range("E" & Cnt2).Value = _
                 Sheets("Sheet1").Range("E" & Cnt2).Value - 1
Cnt2 = Cnt2 + 1
Loop
End If
End If
Next Cnt
End Sub
Dave
 
Upvote 0
missed some brackets....
Code: 
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 Sheets("Sheet1").Range("I" & Cnt).Value = "EXPIRED" Then
Temp = Sheets("Sheet1").Range("E" & Cnt).Value
Cnt2 = Cnt + 1
Do Until (Sheets("Sheet1").Range("E" & Cnt2).Value = Temp) Or _
(Sheets("Sheet1").Range("I" & Cnt2).Value = "EXPIRED")
Sheets("Sheet1").Range("E" & Cnt2).Value = _
                 Sheets("Sheet1").Range("E" & Cnt2).Value - 1
Cnt2 = Cnt2 + 1
Loop
End If
End If
Next Cnt
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
If XLOOKUP value is blank, return blank
Replies
1
Views
678
DanteAmor
DanteAmor
M
  • Question Question
Check if cell is blank with additional criteria
Replies
3
Views
254
Cubist
Cubist
N
  • Solved
Move entire row based on expiration date
2
Replies
17
Views
772
rollis13
rollis13
ExcelNewbie2020
  • Solved
Restructure list form from horizontal to vertical based on condition
Replies
2
Views
69
Fluff
Fluff
H
  • Question Question
Positioning active cell when adding table rows.
Replies
3
Views
183
myall_blues
myall_blues

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top