Elektroda.com
Elektroda.com
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Elektroda.com

Selecting VBA Excel rows -

4179 15
This content has been translated » The original version can be found here
  • Level 5  
    Hi everyone, I have 2 small problems with macro for selecting rows.
    1. The first macro adds a border to the selected row - in this code I would like to add the range in which it is to operate (only within the table) From row 42 to 305.

    Code:

      If w > 0 Then
        With Rows(w)
          .Borders(xlEdgeBottom).LineStyle = xlNone
        End With
      End If

      With Target(1).EntireRow
        w = .Row

        With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(217, 217, 217)
        End With

      End With


    2. This macro adds a row fill color to me - How do I make a row selection color (from VBA code) overlap an existing fill from conditional formatting? at the moment, it doesn't work with color-filled cells.

    Code:

    Rows("42:305").Interior.ColorIndex = xlNone
    If Not Intersect(Target, Rows("42:305")) Is Nothing Then Selection.EntireRow.Interior.Color = RGB(249, 249, 249)


    Can you help?
  • Level 30  
    mpodciwinski wrote:
    ... in this code I would like to add the range in which it is to operate (only within the table) From line 42 to 305 ...
    mpodciwinski wrote:
    With Target (1). EntireRow

    1. Within the table ... what about the columns, never mind?

    2. The fragment above is from Worksheet_SelectionChange (ByVal Target As Range) or Worksheet_Change (ByVal Target As Range)?

    In the code from your point 2 there is a solution:
    Code: vbscript
    Log in, to see the code

    besides, if you don't look at it, there is a 'Target' in both of the above events of the spreadsheet, which you can use - the message below will appear only in the range 'A1 :D 10 ":
    Code: vba
    Log in, to see the code


    mpodciwinski wrote:
    How to make to color from the row selection (from VBA code) superimposed on existing fill from conditional formatting ?

    Then conditional formatting would no longer be conditional ... just standard.
  • Level 5  
    1. I'm only interested in poems.
    2. Private Sub Worksheet_SelectionChange (ByVal Target As Range)

    It may be, but I don't know how to modify this code, I tried it myself, but I still get errors.

    lanzul wrote:
    Code: vba Expand Select all
    Private Sub Worksheet_SelectionChange (ByVal Target As Range)
    If Not Intersect (Target, Range ("a1: d10")) Is Nothing Then
    w = Target
    k = Target.Column
    MsgBox "Row =" & w & "; Column =" & k
    End if
    End Sub


    This code works, but it still has to reverse this procedure, i.e. after clicking another place the previous line fill must disappear (leaving the standard fill)
    How to do it?

    Another question, can these two codes be combined into one? to have fill and border in one code?
  • Level 30  
    mpodciwinski wrote:
    but it still has to reverse this procedure

    The following code works on lines from 42 to 305
    Code: vba
    Log in, to see the code

    and the following works outside the range of these lines, i.e. outside the lines in the range 42 to 305:
    Code: vbscript
    Log in, to see the code
  • Level 5  
    Thanks for the reply, but canceling something doesn't work for me.
    I know it's a breeze for you, but I'm a beginner in VBA and I still have a problem ..

    Code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim w As Long
    Dim zakres As String

        zakres = "42:305"
       
        If Not Intersect(Target, Range(zakres)) Is Nothing Then
            '...
            'jakieś instrukcje, która mają
            'zadziałać w zakresie wierszy od 42 do 305
            'np.:
            Selection.EntireRow.Interior.ColorIndex = 19
            '...
        End If
       
        If Intersect(Target, Range(zakres)) Is Nothing Then
            '...
            'jakieś instrukcje, która mają
            'zadziałać poza zakresem wierszy od 42 do 305
            'np.:
            .Interior.ColorIndex = xlNone
            '...
        End If
    End Sub


    I did, but I get an error ...
    Can I ask you to put together such a ready formula with a fill and border? and her appeal?
    Thank you in advance.
  • Level 30  
    mpodciwinski wrote:
    can these two codes be combined into one?

    You can, by the condition If ... Else ... End If
    Code: vba
    Log in, to see the code

    If in the range ' 42: 305 'the code No. 1 and part of the code No. 2 from entry No. 1 are to work, you have to put it there, another thing that you do not know where the variable from' in 'takes its value there, since it is written that:
    Code: vba
    Log in, to see the code

    after all if ' w = Target ', it will always be bigger than' 0 ", because there is no poem =" 0 "not even a poem" negative ".
    So, we would, for example, from code 1 and part of code 2:
    Code: vba
    Log in, to see the code


    However, in the opposite situation - out of the range '42: 305 '
    mpodciwinski wrote:
    it still has to reverse this procedure, i.e. after clicking another place the previous line fill must disappear (leaving the standard fill)

    Part of the code:
    Code: vba
    Log in, to see the code

    will enter:
    Code: vba
    Log in, to see the code


    After ordering and assuming that ' in 'is the same as the row number in which the cell is selected (i.e. Target '), it could look holistic like this:
    Code: vba
    Log in, to see the code

    though, there are some discrepancies here - what exactly should be the style of the line - and what the total color of the cell filling should be (?)
    Unless there is something else as well?
  • Level 30  
    mpodciwinski wrote:
    this does not reverse the line selection

    ? if it doesn't turn ... it turns, below is a sample on an example of colors:

    https://filmy.elektroda.pl/49_1501844650.mp4
    ( The movie certainly plays through MPC-HC (free software) )

    From the codes (code No. 2, entry No. 1) you have posted, it appears that selecting a cell in the "42: 305" range puts the color of the cell filling on " RGB (249, 249, 249) ", meaning" almost white "(white = 255/2555/255 ):
    Code: vba
    Log in, to see the code

    However, selecting any cell outside the range "42: 305" is to impose on this range the "standard format", i.e. no filling:
    Code: vba
    Log in, to see the code

    or also like this:
    Code: vba
    Log in, to see the code
    Code: vba
    Log in, to see the code

    and info:
    mpodciwinski wrote:
    ... but it still has to reverse this procedure, i.e. after clicking another place the previous line fill must disappear (leaving the standard fill)

    Because "almost white" (249/249/249) almost equals "no color", i.e. white (255/255/255), "almost nothing can be seen" ...: -> ...
    It's best to record a piece of the macro with the colors you would like to have when selecting cells in the "42: 305" range and outside this range, and post here or just enter the colors they want to be.
  • Level 5  
    I see every color change, I have a graphic monitor, but that's not the point.
    Here is a video with a different color:
    https://www.dropbox.com/s/a63ox30nj10wcaa/2017-08-06_13h54_10.mp4?dl=0
    as you can see this is not a color problem.
    Perhaps we are talking about a different action, because you deleted all the table fill
    And it should work like this:
    https://www.dropbox.com/s/nazbiwwll08uoug/2017-08-06_13h55_47.mp4?dl=0
    So the whole line is marked and only it, immediately after clicking in another place, the previously selected one returns to normal state and the one you clicked is highlighted (line)
  • Level 30  
    mpodciwinski wrote:
    And it should work like this:
    https: // ....
    So the whole line is marked and only it, immediately after clicking in another place, the previously selected one returns to normal state and the one you clicked is highlighted (line)

    All right, but I can see that you have already dealt with it, so what else is the matter?
  • Level 5  
    This is an old formula that works only with filling, I pasted it at the beginning of the thread (2).
    It doesn't work with borders ..: /
    Can you help me with this problem? ;)
  • Level 30  
    mpodciwinski wrote:
    It doesn't work with borders

    A description of the action:
    1. In the range of lines '42: 305 ', selecting any cell is to call:
    a. complete the entire row, with the cell marked, "some color" (no matter what at the moment)
    b. putting a line border (at least on the bottom edge)
    c. deleting row filling out of range '42: 305 ', in which row some cell was previously marked

    2. Outside the range of lines '42: 305 ', selecting any cell is to call:
    a. complete the entire row, with the cell marked, "some color" (no matter what at the moment)
    b. removing the border of the poem (at least from the bottom edge) from the range '42: 305 '
    c. removing row filling from the range '42: 305 ', in which row some cell was previously marked

    Yes or something else? If otherwise write in the points.
  • Level 5  
    this is how the macro should work:
    1. In the range of lines '42: 305 ', selecting any cell is to call:
    a. complete the entire row, with the cell marked, "some color" (no matter what at the moment)
    b. putting a line border (at least on the bottom edge)
    c. removing the fill from the previously selected line in the range '42: 305 '
    d. removing the border from the previously selected line in the range '42: 305 '
    2. out of range nothing happens.
  • Level 30  
    mpodciwinski wrote:
    this is how the macro should work:
    1. In the range of lines '42: 305 ', selecting any cell is to call:
    a. complete the entire row, with the cell marked, "some color" (no matter what at the moment)
    b. putting a line border (at least on the bottom edge)
    c. removing the fill from the previously selected line in the range '42: 305 '
    d. removing the border from the previously selected line in the range '42: 305 '
    2. out of range nothing happens.

    The sheet code below:
    Code: vba
    Log in, to see the code

    Below is the "possible" code to paste into "ThisWorkbook" if you wanted to experiment with the unlocked comments in the code above and see how it works in various "layouts":
    Code: vba
    Log in, to see the code

    You can still "dress" it with the "With ... End With" construction, but then everything will become "longer" and may be less understood.

    Ps: This is my "understanding" of the topic, other colleagues from the forum would certainly do it differently, probably simpler and better, so if I missed it, wait, maybe a friend will say: JRV . adamas_nt . Prot , others I haven't mentioned ...
  • Level 5  
    You could say it works with one exception. After clicking on another place, a light blue border from the top and bottom is left whose color is not even in the code.
    Selecting VBA Excel rows -

    And I have a question, what is a "diary" that refers to cell A1?
  • Level 30  
    mpodciwinski wrote:
    what is the "diary ... cell A1"

    The output value for the macro doesn't bother
    mpodciwinski wrote:
    a light blue border remains in a different place

    I don't know ... with the grid lines removed (Tools \ Options \ Grid lines) nothing remains ...