none
VBA code - change value based on another value RRS feed

  • Question

  • I'm trying to get this code to work, to display a message in Sheet1.B32 if the total row (K28) changes value

    This is in sheet1. K28 is the total row

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$K$28" Then
        Call ULWord
    End If
    End Sub


    and in module (this works when manually running the macro). One of the words must be underlined.

    Sub ULWord()
        If Sheet1.Range("K28") > Sheet2.Range("maxMonthly") Then
            Sheet1.Range("B32") = "This forum is for Developer discussions and questions involving Microsoft Excel."
            Sheet1.Range("B32").Characters(19, 9).Font.Underline = True
        Else
            Sheet1.Range("B32") = " "
        End If
    End Sub

    It's not giving me an error message but it doesn't work either.

    Thanks.



    • Edited by Tamras72 Thursday, May 18, 2017 4:42 PM
    Thursday, May 18, 2017 4:39 PM

All replies

  • I think your issue is caused by that, total row is calculated cell, if you change other cells, the value changes, but it would not target address is not “K28”.

    I suggest you try below code:

    Public value As String
    
    Private Sub Worksheet_Calculate()
    If Range("K28") <> value Then
        Call ULWord
        value = Range("K28")
    End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    value = Range("K28")
    End Sub
    
    Sub ULWord()
        If Sheet1.Range("K28") > 10 Then
            Sheet1.Range("B32") = "This forum is for Developer discussions and questions involving Microsoft Excel."
            Sheet1.Range("B32").Characters(19, 9).Font.Underline = True
        Else
            Sheet1.Range("B32") = " "
        End If
    End Sub
    


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 24, 2017 10:59 AM