Announcement

Collapse
No announcement yet.

Use Active Cell Row To Check Another Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use Active Cell Row To Check Another Column

    Hi all, I've been racking my brains trying to figure this out but cant, please can someone assist. I need to write some vba code that looks at cells in column aa and ag (on the same row) and displays a message is ag > 0 and aa <> "Agency".

    Thanks!

  • #2
    Re: Checking Cell Status

    What about Conditional Formatting?
    Last edited by Dave Hawley; December 23rd, 2006, 09:47.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Checking Cell Status

      Thought about conditional formatting but need it to pop up a msbgox if it meets the criteria - can i only do this by vba?

      Comment


      • #4
        Re: Checking Cell Status

        Data Validation can be used to pop up messages, otherwise VBA
        Last edited by Dave Hawley; December 23rd, 2006, 09:48.
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          Re: Checking Cell Status

          Sorry, to explain what I mean better here part of the sub that I'm going to put the new vba in:

          Code:
          Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
              If ActiveCell.Column = 21 Then
                  If Cells(ActiveCell.Row, ActiveCell.Column - 19) <> "" Then
          The new If statement needs to find out what is in 2 specific columns on the same row wherever the active cell is.

          Comment


          • #6
            Re: Checking Cell Status

            Hi Spud,

            Carrying on from your code, the following should do it;

            Code:
            Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
                If ActiveCell.Column = 21 Then
                    If Cells(ActiveCell.Row, ActiveCell.Column - 19) <> "" Then
                        If Range("AA" & ActiveCell.Row) <> "Agency" And Range("AG" & ActiveCell.Row) > 0 Then MsgBox "This is the message", vbCritical
                    End If
                End If
            End Sub
            Bill

            Comment


            • #7
              Re: Checking Cell Status

              This works great thanks, though how do I get it to search in all rows between 12 and 3000 instead of all? thanks again!

              Comment


              • #8
                Re: Checking Cell Status

                Hi Spud,

                Try the following:
                Code:
                Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
                    If Target.Column<>21 Then Exit Sub
                    If Target.Row < 12 Or Target.Row > 3000 Then Exit Sub
                    If ActiveCell.Column = 21 Then
                        If Cells(ActiveCell.Row, ActiveCell.Column - 19) <> "" Then
                            If Range("AA" & ActiveCell.Row) <> "Agency" And Range("AG" & ActiveCell.Row) > 0 Then MsgBox "This is the message", vbCritical
                        End If
                    End If
                End Sub
                Last edited by Ranger; December 22nd, 2006, 23:28.

                Comment


                • #9
                  Re: Checking Cell Status

                  Hi Spud,

                  Probably the following just makes it a little cleaner:

                  Code:
                  Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
                      If Target.Row < 12 Or Target.Row > 3000 Then Exit Sub
                      If Target.Column <> 21 Then Exit Sub
                          If Cells(Target.Row, Target.Column - 19) <> "" Then
                              If Range("AA" & Target.Row) <> "Agency" And Range("AG" & Target.Row) > 0 Then MsgBox "This is the message", vbCritical
                          End If
                  End Sub
                  Bill

                  Comment

                  Working...
                  X