logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Selecting VBA Excel Rows - Macro to Add Borders and Row Fill within Table Range (42-305) & Overlap

mpodciwinski 7446 15
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Reply | New topic
  • #1 16610907
    mpodciwinski
    Level 7  
    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: text Expand Select all Copy to clipboard
    
  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: text Expand Select all Copy to clipboard
    
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?
    Rust on Embedded Online Conference on 16 July with 33 per cent discount for elektroda users
  • ADVERTISEMENT
  • #2 16614287
    lanzul
    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: text
    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.
  • #3 16617610
    mpodciwinski
    Level 7  
    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?
  • #4 16618191
    lanzul
    Level 30  
    mpodciwinski wrote:
    but it still has to reverse this procedure

    The following code works on lines from 42 to 305
    Code: text
    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
  • ADVERTISEMENT
  • #5 16618254
    mpodciwinski
    Level 7  
    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: text Expand Select all Copy to clipboard
    
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.
  • #6 16623697
    lanzul
    Level 30  
    mpodciwinski wrote:
    can these two codes be combined into one?

    You can, by the condition If ... Else ... End If
    Code: text
    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: text
    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: text
    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: text
    Log in, to see the code

    will enter:
    Code: text
    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: text
    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?
  • ADVERTISEMENT
  • ADVERTISEMENT
  • #8 16624829
    lanzul
    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: text
    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: text
    Log in, to see the code

    or also like this:
    Code: text
    Log in, to see the code
    Code: text
    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.
  • #9 16627757
    mpodciwinski
    Level 7  
    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)
  • #10 16628187
    lanzul
    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?
  • #11 16629099
    mpodciwinski
    Level 7  
    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? ;)
  • #12 16629482
    lanzul
    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.
  • #13 16629542
    mpodciwinski
    Level 7  
    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.
  • #14 16629857
    lanzul
    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: text
    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: text
    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 ...
  • #15 16630856
    mpodciwinski
    Level 7  
    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 - Macro to Add Borders and Row Fill within Table Range (42-305) & Overlap

    And I have a question, what is a "diary" that refers to cell A1?
  • #16 16654050
    lanzul
    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 ...
Reply | New topic
Report a violation of the law

Topic summary

The discussion revolves around creating a VBA macro in Excel to manage row selection within a specified range (rows 42 to 305). The user seeks to add borders and fill colors to selected rows while ensuring that previous selections revert to their original state. Key issues include modifying the macro to operate strictly within the defined range and addressing conflicts with existing conditional formatting. Various code snippets and solutions are proposed, including the use of the Worksheet_SelectionChange event to handle row formatting dynamically based on user interaction. The conversation highlights the need for a combined approach to manage both fill colors and borders effectively, while also ensuring that previous selections are cleared upon new selections.
Summary generated by the language model.
ADVERTISEMENT