Display text in column

[adamsm]

Hi anyone,

How could I make the following code to display the text in the code on column "F" below the last data row of column "C"; when the user changes the value in cell "J10"

Dim m As Long
    If Not Range("J10") = "" Then
    With Worksheets("MySheet")
        m = .Cells(.Rows.Count, 1).End(xlUp).Row
        If Not .Cells(m, 7) = "Welcome" Then
            .Cells(m + 1, 7) = "Welcome"
            .Cells(m + 1, 7).Font.Bold = True
            End If
    End With
End If

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Pediez]

In the worksheet module enter this code and try

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J10")) Is Nothing Then
 'Do your activity here....[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New] 

End If
End Sub

 

[adamsm]

Thanks for the reply. But the code still does not place the text below the last data row of column C, in column F.

How could I achieve this?

 

[pedie]

Thanks for the reply. But the code still does not place the text below the last data row of column C, in column F.

How could I achieve this?

Do you want to place the word J10 in the last row of col C? Is that what we are wanting?

 

[pedie]

From your worksheet press/hit Alt + F11 >> hit Ctrl + R >> you'll see your sheet name on the left handside, right click on it then click on view code >>
paste this code there in the blank white space....

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J10")) Is Nothing Then
 Application.EnableEvents = False
 Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("J10").Value
 Application.EnableEvents = True
End If
End Sub

Then try changing values in J10 of the sheet....

 

[adamsm]

Thanks for the reply. Your code is placing the value that is on cell J10 below the last data row of column C in Column C.


What I want is to place the text "welcome" below the last data row of column C in column F.

I hope I've made my question clear.

Any help would be kindly appreciated.

Thanks in advance.

 

[pedie]

not tested...

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=Courier New]If Not Intersect(Target, Range("J10")) Is Nothing Then[/FONT]
[FONT=Courier New]Application.EnableEvents = False[/FONT]
[FONT=Courier New]Dim m As Long
    If Not Range("J10") = "" Then
    With Worksheets("MySheet")
        m = .Cells(.Rows.Count, 1).End(xlUp).Row
        If Not .Cells(m, 7) = "Welcome" Then
            .Cells(m + 1, 7) = "Welcome"
            .Cells(m + 1, 7).Font.Bold = True
            End If
    End With
End If[/FONT]
[FONT=Courier New]Application.EnableEvents = True[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]End Sub[/FONT]
[FONT=Courier New]

 

[adamsm]

This is the same code that I've posted at the beginning of the post which does insert the text "welcome" in column 7 in reference to the last row in column 1. I want to insert the text "welcome" in column 7 in reference to the last data row is column 3 not column 1.

I hope I've made my question clear.

Any help would be kindly appreciated.

 

[adamsm]

The following modification worked for me. Thanks for the help.

[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT]
[FONT=Courier New]If Not Intersect(Target, Range("J10")) Is Nothing Then[/FONT]
[FONT=Courier New]Application.EnableEvents = False[/FONT]
[FONT=Courier New]Dim m As Long
    If Not Range("J10") = "" Then
    With Worksheets("MySheet")
        m = .Cells(.Rows.Count, 3).End(xlUp).Row
        If Not .Cells(m, 7) = "Welcome" Then
            .Cells(m + 1, 7) = "Welcome"
            .Cells(m + 1, 7).Font.Bold = True
            End If
    End With
End If[/FONT]
[FONT=Courier New]Application.EnableEvents = True[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[pedie]

I just tried this code....and it looking at col C it places Welcome in Col G, corresponding to col C adding 1 whcih is row
If you want it to be in same row then remove m +1 and make it just m,7

I have no idea what you're trying to do...

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J10")) Is Nothing Then
Application.EnableEvents = False
Dim m As Long
    If Not Range("J10") = "" Then
    With Worksheets("Sheet1")
        m = .Cells(.Rows.Count, 3).End(xlUp).Row
        If Not .Cells(m, 7) = "Welcome" Then
            .Cells(m + 1, 7) = "Welcome"
            .Cells(m + 1, 7).Font.Bold = True
            End If
    End With
End If
Application.EnableEvents = True
End If
End Sub