Selecting VBA Excel rows -

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.



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.



Can you help?

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:

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 10 ":

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.

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?

mpodciwinski wrote:

but it still has to reverse this procedure

The following code works on lines from 42 to 305

and the following works outside the range of these lines, i.e. outside the lines in the range 42 to 305:

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



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.

mpodciwinski wrote:

can these two codes be combined into one?

You can, by the condition If ... Else ... End If

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:

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:


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:

will enter:


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:

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?

Thanks so much for the explanation!
Now only one thing doesn't work, it doesn't reverse the line selection:
https://www.dropbox.com/s/oujm0aj0pzmx4d4/2017-08-04_09h57_22.mp4?dl=0

The filling remains on the selected lines.

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

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

or also like this:

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.

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)

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?

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?

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.

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.

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:

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

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

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.


And I have a question, what is a "diary" that refers to cell A1?

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