X Marks the spot

[Lester601]

I have to two columns
Stating at range A9:A5009
The "A" column has the number"1" on each row all the way to A5009
Column N, range N9:5009 can have the value "X" or blank/empty cell.

What I want happen is when an "X" is placed in a cell in column N it triggers the delete or the number "1" in Colum A on the very row where the "X" was found.

If the cell column A is blank then it puts the number "1" on the very row in Column A

Any help please

[just_jon]

I have to two columns
Stating at range A9:A5009
The "A" column has the number"1" on each row all the way to A5009
Column N, range N9:5009 can have the value "X" or blank/empty cell.

What I want happen is when an "X" is placed in a cell in column N it triggers the delete or the number "1" in Colum A on the very row where the "X" was found.

If the cell column A is blank then it puts the number "1" on the very row in Column A

Any help please

If you don't mind a formula approach, in A9, copied down to A5009:

=IF(N9="X","",IF(N="",1,"what goes in A if N<>X AND N <> Null"))

You might want to use, depending on what possible values may be contained within Nx:

=IF(N9="X","",IF(ISBLANK(A9),1,"what goes in A if N<>X AND N <> Null"))

[Lester601]

I rather use code. But thanks for the formula.

This is the code i have so far, but it seems to error. Base on my original question can someone help me fix it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngA As Range, rngB As Range, rngT As Range
Dim i As Integer
Dim v As String

Set rngB = Range("N9:N5009")

'look if we're doing something in column N
If Not Intersect(Target, rngB) Is Nothing And Target.Value = "X" Then
Set rngA = Range("A9:A5009")
'get the row number
i = Target.Row
'check if A was empty if so then put in a 1 else delete the 1
If rngA(i, 1).Value = "" Then rngA(i, 1).Value = 1 Else rngA(i, 1).Value = ""
Set rngA = Nothing
End If

Set rngB = Nothing

End Sub

[just_jon]

Have you tried to debug the code by stepping-thru it?

[Tom Urtis]

If the word "or" should have been "of" in this excerpt from your first post...

"when an "X" is placed in a cell in column N it triggers the delete or the number "1" in Colum A on the very row where the "X" was found."

...then see if this gets you any closer, instead of the code you are using.


Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("N9:N5009")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "X" Then Exit Sub
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
ActiveSheet.AutoFilterMode = False
Dim ExRange As Range, OneRange As Range
Set ExRange = Range("A8:N5009")
Set OneRange = Range("A8:A5009")
ExRange.AutoFilter Field:=14, Criteria1:="X"
OneRange.SpecialCells(xlCellTypeVisible).Replace What:="1", Replacement:="", LookAt:=xlWhole
ExRange.AutoFilter Field:=14, Criteria1:="<>X", Operator:=xlAnd
OneRange.SpecialCells(xlCellTypeBlanks).Value = "1"
ActiveSheet.AutoFilterMode = False
With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With
End Sub