Clear Cell directly to right when cell to left changes value

PrincessApril · #3 08-03-2020, 09:58 PM

Quote:

Originally Posted by Logit

.
This is one of many methods :

In the Sheet Module paste :

Code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   TestInRange
...

Thank you for the reply Logit. This looks very close but seems to trigger when I select the cell (rather than when I change the value, which is what I'm looking for).

I tried changing Worksheet_SelectionChange to just Worksheet_Change but that gave me this error, and when I click Debug I can only briefly see the error before Excel crashes.

After some research, I was able to prevent the error and crashing by temporarily disabling events, with this:

Code:

Sub TestInRange()
Application.EnableEvents = False
    If InRange(ActiveCell, Range("H6:H81")) Then
        ' code to handle that the active cell is within the right range
        ActiveCell.Offset(0, 1).Value = ""
    Else
        Exit Sub
    End If
Application.EnableEvents = True
End Sub

However, my attempt to use Worksheet_Change does not seem to be clearing the cell directly to the right (e.g., if I change the value in H13, then the value in I13 should clear, or if I change the value in H22, then the value in I22 should clear). Right now (with the changes mentioned here) the value in column I is staying the same rather than clearing.

Not sure if the following matters but just in case:
-I have two hidden columns (F and G) in this worksheet.
-Column H is a date with the validiation -AND(ISNUMBER($H6),LEFT(CELL("format",$H6),1)="D")
-Column I is a dropdown list of text values with validation that pulls from a reference table in another worksheet

After having many solutions posted to various forums with none working, I'm beginning to suspect that the second bullet might be the issue. Perhaps we have to call the formula property rather than the value property? If so, that I am not sure how to do

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Retaining the left side space in table cell	kumarjeyabalan	Word Tables	2	08-17-2019 03:14 AM
Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow	FUGMAN	Excel Programming	7	02-05-2017 08:37 AM
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C	rick10r	Excel	1	07-05-2016 12:07 PM
Assign the value of a cell as a cell reference of another cell in Excel 2010 - How to?	bharathkumarst	Excel	7	10-13-2014 10:25 AM
How do I align image at ABSOLUTE top left of a table cell?	shnoozin	Outlook	0	07-22-2011 03:30 PM