#1
|
|||
|
|||
Clear Cell directly to right when cell to left changes value
Hi all,
I'm trying to clear the cell to the right (column I, range 6-81) when the cell to the left (column H, range is 6-81) changes value. Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("H$6")) Is Nothing Then Range("I$6").ClearContents End If End Sub I tried using a colon I$6:I$81 and H$6:H$81 but that cleared the whole column (and I only want to clear the cell directly to the right upon a changed value to the cell directly to the left; i.e., same row). I thought I was using the dollar sign correctly but perhaps not? |
#2
|
|||
|
|||
.
This is one of many methods : In the Sheet Module paste : Code:
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) TestInRange End Sub In a Regular Module paste : Code:
Option Explicit Function InRange(Range1 As Range, Range2 As Range) As Boolean ' returns True if Range1 is within Range2 Dim InterSectRange As Range Set InterSectRange = Application.Intersect(Range1, Range2) InRange = Not InterSectRange Is Nothing Set InterSectRange = Nothing End Function Sub TestInRange() 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 End Sub |
#3
|
|||
|
|||
Quote:
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 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 Last edited by PrincessApril; 08-03-2020 at 11:39 PM. Reason: clarity |
#4
|
||||
|
||||
Code:
Private Sub Worksheet_Change(ByVal Target As Range) Set rng = Intersect(Target, Range("H6:H81")) If Not rng Is Nothing Then For Each cll In rng.Cells cll.Offset(, 1).ClearContents Next cll End If End Sub |
#5
|
|||
|
|||
Must be something about my sheet. Still no clear in I upon value change in H:
Before Change: Screenshot by Lightshot After Change (I is Same): Screenshot by Lightshot |
#6
|
||||
|
||||
You've put the code the sheet concerned's code module and not in another module?
If so, you'd better attach a workbook (desensitized) here, or send me a copy privately (ask me for an email address via Private Message here) if you're happy for just me to see it. |
#7
|
|||
|
|||
Got it. Did some house cleaning and removed something that was conflicting. Not actually sure what it was because I removed a bunch of stuff no longer needed.
Had an extraneous Worksheet Change Sub later down in the sheet from previous work, as well as some workbook code that was no longer needed. Removed both along with the hidden columns and one of those did the trick. Thank you both kindly. For others for future reference, I opted for p45's in the end as it seemed a bit lighter on a few initial tests for my particular setup. |
#8
|
||||
|
||||
You can safely hide the columns again.
|
|
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 |