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
