Results 1 to 3 of 3

Thread: Change contents based on another cell not working

  1. #1

    Change contents based on another cell not working



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi good people!,

    M very first time here, so...Hello!!.. :-)

    I have two codes, the one hides columns when cell value goes to "0", and the other IS SUPPOSED to change cell content based on this "0". The columns hide perfectly well, but the range is not altered. I have tried more than 1 "version" for the second code, but just no joy. Please help me with the second code?
    ALSO, the reason for pasting both codes is to ask if anyone could merge them together maybe?

    [CODEPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    Set Target = Intersect(Target, Target.Parent.Rows(2))
    If Not Target Is Nothing Then
    For Each Cell In Target
    Cell.EntireColumn.Hidden = (Cell.Value = 0)
    Next Cell
    End If
    End Sub
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "B2" Then
    If Target.Value = 0 Then
    Range("B3:B137").Value = [na()]

    End If
    End Sub][/CODE]

    All and any help will be accepted with great appreciation!..Thank you..

  2. #2
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    132
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    This is untested air code, but try:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Set Target = Intersect(Target, Rows(2))
        If Not Target Is Nothing Then
            For Each Cell In Target
                If Cell.Value = 0 Then Range(Cell.Offset(1), Cells(137, Cell.Column)).Value = "N/A"
                Cell.EntireColumn.Hidden = (Cell.Value = 0)
            Next Cell
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Last edited by Rudi; 2015-06-14 at 01:35 PM.
    Regards,
    Rudi
    www.eileenslounge.com

  3. #3
    Works perfect!..Thank you very much!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •