Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Use Active Cell Row To Check Another Column

  1. #1
    Join Date
    8th September 2006
    Posts
    61

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,870

    Re: Checking Cell Status

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

    Roy

    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.

  3. #3
    Join Date
    8th September 2006
    Posts
    61

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,870

    Re: Checking Cell Status

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

    Roy

    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.

  5. #5
    Join Date
    8th September 2006
    Posts
    61

    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:

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Checking Cell Status

    Hi Spud,

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

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th September 2006
    Posts
    61

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Checking Cell Status

    Hi Spud,

    Try the following:
    VB:
    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 at 22:28.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th January 2003
    Location
    Scotland
    Posts
    1,462

    Re: Checking Cell Status

    Hi Spud,

    Probably the following just makes it a little cleaner:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. First & Last Used Cell In Active Row / Column
    By superik84 in forum EXCEL HELP
    Replies: 3
    Last Post: February 23rd, 2008, 02:21
  2. Check Active Cell Before Running Code
    By Jaffey in forum EXCEL HELP
    Replies: 11
    Last Post: December 14th, 2007, 06:00
  3. Check Cell On Non Active Worksheet
    By Mfergus in forum EXCEL HELP
    Replies: 2
    Last Post: December 6th, 2007, 12:44
  4. Sort By Active Cell Column
    By kwidlake in forum EXCEL HELP
    Replies: 1
    Last Post: July 17th, 2007, 14:56
  5. Pass Value From Active Cell Row but not Column
    By minitman in forum EXCEL HELP
    Replies: 3
    Last Post: September 8th, 2006, 10:30

Bookmarks

Posting Permissions

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