none
get the row number of the selected cell

    Question

  • Hi - I have a .xls  with vb code to perform soame ations. I need you help to:

    1. how can i get the select cells' row number and attach it a variable

    2. Once i get the row number, how do i get the value so i can compare it with valid values.

    Thursday, April 19, 2012 5:37 PM

Answers

  • Greetings,

    Here is a bit of code that should do what you need.

    First, I am declaring a variable that will store the row number for the active cell.  Then, I am using the Row property of the active cell to determine the row number.  Finally, I have some example code that fetches the row number from the variable and tests it to see if the row number is even or odd.

    Sub GetRowNumber()
      'Create a variable to store the row number.
      Dim lngCurrentRowNumber As Long
     
      'Get the current row number and assign it to lngCurrentRowNumber.
      lngCurrentRowNumber = ActiveCell.Row
     
      'Test the value.  If the row is an even number, change the background color of the row.
     
      If lngCurrentRowNumber Mod 2 = 0 Then
        'The mod operator returns the remainder after doing division. If the row is 2, 2/2= 1
        'and there is no remainder, thus this is an even row.
        ActiveCell.EntireRow.Interior.Color = vbYellow
      End If
    End Sub

    Please let me know if this have or if you have additional questions.  If you found this post helpful please vote for it as helpful and/or mark it as an answer.  Thanks!

    Mike Corkery, MCT, MCPD, MCITP, MCDBA, MCSD, MSF, etc.

    Thursday, April 19, 2012 6:11 PM

All replies

  • Greetings,

    Here is a bit of code that should do what you need.

    First, I am declaring a variable that will store the row number for the active cell.  Then, I am using the Row property of the active cell to determine the row number.  Finally, I have some example code that fetches the row number from the variable and tests it to see if the row number is even or odd.

    Sub GetRowNumber()
      'Create a variable to store the row number.
      Dim lngCurrentRowNumber As Long
     
      'Get the current row number and assign it to lngCurrentRowNumber.
      lngCurrentRowNumber = ActiveCell.Row
     
      'Test the value.  If the row is an even number, change the background color of the row.
     
      If lngCurrentRowNumber Mod 2 = 0 Then
        'The mod operator returns the remainder after doing division. If the row is 2, 2/2= 1
        'and there is no remainder, thus this is an even row.
        ActiveCell.EntireRow.Interior.Color = vbYellow
      End If
    End Sub

    Please let me know if this have or if you have additional questions.  If you found this post helpful please vote for it as helpful and/or mark it as an answer.  Thanks!

    Mike Corkery, MCT, MCPD, MCITP, MCDBA, MCSD, MSF, etc.

    Thursday, April 19, 2012 6:11 PM
  • Thanks you so much! this worked as expected.
    Thursday, April 19, 2012 9:37 PM