Change value of a certain cell and will display or not a certain text in a range of cells

[beapotz10]

I really have never used vba before but i am really eager to nail this one.

What I want to happen is if I input "X" to cell E7, it will show the word same in a range of cells (ex: H1:H50) and if I delete the "X" on E7, the text will also be removed.

Please please help

 

[steve the fish]

Right click on the sheet tab. Press view code. In the 'white space' paste this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Application.EnableEvents = False

Set rng = Intersect(Target, Range("E7"))

If Not rng Is Nothing Then
    If Len(rng.Value) > 0 Then
        Range("H1:H50") = rng.Value
    Else
        Range("H1:H50").ClearContents
    End If
End If

Application.EnableEvents = True

End Sub

See if that works for you.

 

[beapotz10]

WORKED LIKE A CHARM! thank you very much Steve! :*

 

[beapotz10]

HELP! after I saved the file, it doesn't work anymore

 

[steve the fish]

Well saving it isnt going to make any difference presuming of course you saved it with the code in place and the file is a macro enabled workbook with macros duly enabled. Check the code is still in the worksheet module.

 

[beapotz10]

Figured it out, Tee-hee! THanks a million! Hugs from Dubai!