Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-26-2020, 11:56 AM
PrincessApril PrincessApril is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Competent Performer
Clear Cell directly to right when cell to left changes value
 
Join Date: Nov 2019
Posts: 102
PrincessApril is on a distinguished road
Default 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 have it working for the pair of cells in H6/I6, but I'm unsure how to apply the same code to the whole range so that a change in any given cell in column H will clear the data from only the cell directly to the right of it in column I (but not the whole column).

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?
Reply With Quote
  #2  
Old 06-26-2020, 03:47 PM
Logit Logit is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
Reply With Quote
  #3  
Old 08-03-2020, 09:58 PM
PrincessApril PrincessApril is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Competent Performer
Clear Cell directly to right when cell to left changes value
 
Join Date: Nov 2019
Posts: 102
PrincessApril is on a distinguished road
Default

Quote:
Originally Posted by Logit View Post
.
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

Last edited by PrincessApril; 08-03-2020 at 11:39 PM. Reason: clarity
Reply With Quote
  #4  
Old 08-04-2020, 05:05 AM
p45cal's Avatar
p45cal p45cal is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
  #5  
Old 08-04-2020, 06:02 AM
PrincessApril PrincessApril is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Competent Performer
Clear Cell directly to right when cell to left changes value
 
Join Date: Nov 2019
Posts: 102
PrincessApril is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 08-04-2020, 07:14 AM
p45cal's Avatar
p45cal p45cal is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #7  
Old 08-04-2020, 02:42 PM
PrincessApril PrincessApril is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Competent Performer
Clear Cell directly to right when cell to left changes value
 
Join Date: Nov 2019
Posts: 102
PrincessApril is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 08-04-2020, 03:24 PM
p45cal's Avatar
p45cal p45cal is offline Clear Cell directly to right when cell to left changes value Windows 10 Clear Cell directly to right when cell to left changes value Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by PrincessApril View Post
Removed both along with the hidden columns and one of those did the trick.
You can safely hide the columns again.
Reply With Quote
Reply

Thread Tools
Display Modes


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 Cell directly to right when cell to left changes value 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
Clear Cell directly to right when cell to left changes value 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
Clear Cell directly to right when cell to left changes value 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:11 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft