Hi Experts,
I am using the following code in Sheet1
I have Sheet 1 and Sheet 2.
In Sheet 1, the values in B2:D2 are populated from Sheet2. The values in E2:F2 are with formula based on entries in B2:D2. And in G2: I2 I have drop down entries using data validation( which I will insert manually).
Now as per the code , in Sheet 2 : If I revert the entry from Alphabet to Number in Sheet 2 , it will remove the entries from B2: F2 and keep it as blank cells in Sheet 1. When it happens I want G2:I2 entries to go blank as well, but still ready for re-entry. The code works perfectly when it happens with the last Row entry in sheet 1- Row 4. It removes all entries from G4:I4 as blank , still with dropdown feature
But if I change the reversion in Sheet 2 (Alphabet to Number), say it belongs to Row 2 in Sheet1, it shows as below
It works here, as it intended for Row 2, But when such type of action happens between the rows, due to other Code set up in sheet 2, the respective cells from Column B to Column F is being shifted up without disturbing the SL NO column (stays constant as intended). It shows the table view like above, though it removes the entry from Row 2, it removes dropdown entry from one row below Cells from G-H-I column and keeping the last row unchanged.
I want it to be aligned each other from left to right cells and if I revert any change in Sheet 2 , which belongs to any middle row in sheet 1, then the respective cells only needed as blank, and the bottom cells to be realigned again with respective row entries.
Any thoughts-solution , will be grateful
I am using the following code in Sheet1
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("B")) Is Nothing Then Exit Sub
Set Target = Target.Cells(1)
Application.EnableEvents = False
If Target = "" Then
Target.Offset(, 5) = ""
End If
Application.EnableEvents = True
End Sub
I have Sheet 1 and Sheet 2.
In Sheet 1, the values in B2:D2 are populated from Sheet2. The values in E2:F2 are with formula based on entries in B2:D2. And in G2: I2 I have drop down entries using data validation( which I will insert manually).
Now as per the code , in Sheet 2 : If I revert the entry from Alphabet to Number in Sheet 2 , it will remove the entries from B2: F2 and keep it as blank cells in Sheet 1. When it happens I want G2:I2 entries to go blank as well, but still ready for re-entry. The code works perfectly when it happens with the last Row entry in sheet 1- Row 4. It removes all entries from G4:I4 as blank , still with dropdown feature
But if I change the reversion in Sheet 2 (Alphabet to Number), say it belongs to Row 2 in Sheet1, it shows as below
It works here, as it intended for Row 2, But when such type of action happens between the rows, due to other Code set up in sheet 2, the respective cells from Column B to Column F is being shifted up without disturbing the SL NO column (stays constant as intended). It shows the table view like above, though it removes the entry from Row 2, it removes dropdown entry from one row below Cells from G-H-I column and keeping the last row unchanged.
I want it to be aligned each other from left to right cells and if I revert any change in Sheet 2 , which belongs to any middle row in sheet 1, then the respective cells only needed as blank, and the bottom cells to be realigned again with respective row entries.
Any thoughts-solution , will be grateful