Color 3 cells.

harzer · Oct 11, 2024

Hello everyone,
Given my level in vba, it is impossible for me to find a solution to my problem, hence my request.
My current table that I am working on starts (always) at cell "L2" and ends in my case here at cell "AB15", however, it is variable, its rows or columns can vary by increasing or decreasing, hence the need to determine its size.
Now that we know the size of the table, I want when I select a cell in the range ("M3:AB15"), this active cell will be colored green as well as two other cells that are related to this active cell, let me explain:
The two other cells to be colored are at the intersection of the active cell with the cell that is in column "L" (Horizontally) and also the one in row no. 2 (Vertically).
An example is worth a thousand words, I will take two examples:
In the first example, my active cell is cell "P7", we can see that the two cells that intersect with this cell are located at cells "P2" and "L8".
In the 2nd example, my active cell is cell "Y11", we can see that the two cells that intersect with this cell are located at cells "Y2" and "L11".
In summary, each time you click on the macro button, you should have 3 colored cells, the active cell and the 2 intersecting cells in column "L" and in row no. 2.
Of course, when I run the Macro for a new active cell, the old colored cells will no longer be.
I remain at your disposal for any additional information.
Thank you in advance for your suggestions

Colorer 3 cellules.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1

mère

2

père

AET27-037/2022 F

AET27-038/2022 F

AET27-009/2023 F

AET27-010/2023 F

AET27-015/2023 F

AET27-017/2023 F

AET27-019/2023 F

AET27-020/2023 F

AET27-023/2023 F

AET27-055/2023 F

AET27-045/2022 F

AET27-026/2022 F

AET27-010/2021 F

AET2727-034/2021 F

AET27-007/2023 F

AET27-027/2023 F

3

AET27-008/2021 M

2,00%

14,50%

27,60%

14,50%

14,20%

28,10%

1,70%

28,10%

3,10%

1,60%

27,70%

4

AET27-018/2022 M

28,10%

5,00%

15,10%

5,00%

6,90%

2,00%

6,70%

2,00%

1,60%

0,20%

2,90%

5

AET27-019/2022 M

28,10%

5,00%

15,10%

5,00%

6,90%

2,00%

6,70%

2,00%

1,60%

0,20%

2,90%

6

AET27-022/2022 M

8,00%

26,30%

4,50%

26,30%

7,50%

0,90%

7,00%

0,90%

0,00%

0,50%

4,90%

7

AET27-033/2022 M

4,60%

4,10%

3,30%

4,10%

1,30%

2,10%

3,40%

2,10%

28,10%

3,80%

5,60%

8

AET27-036/2023 M

2,30%

2,10%

exemple1

1,70%

2,10%

0,60%

1,10%

4,80%

1,10%

18,80%

11,30%

2,80%

9

AET27-069/2023 M

15,10%

9,80%

15,10%

9,80%

10,50%

27,60%

4,20%

15,10%

2,30%

0,90%

12,20%

10

AET27-022/2023 M

5,00%

26,70%

9,80%

26,70%

17,10%

14,50%

4,40%

27,00%

1,60%

1,00%

13,20%

11

AET27-035/2022 M

28,10%

5,00%

15,10%

5,00%

6,90%

2,00%

6,70%

Exemple2

1,60%

0,20%

2,90%

12

AET27-010/2021 M

7,60%

6,70%

4,30%

6,70%

1,00%

1,10%

0,50%

1,10%

0,00%

0,50%

8,80%

13

AET27-001/2023 M

0,00%

14

AET27-060/2023 M

0,00%

15

AET27-020/2023 M

6,60%

2,00%

3,80%

2,00%

6,10%

0,90%

9,60%

0,90%

8,60%

10,00%

0,80%

Feuil1

Sergius · Oct 11, 2024

Hello! There is this code

VBA Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Cells.Interior.ColorIndex = xlNone
    With ActiveCell
        .EntireRow.Interior.ColorIndex = 35
        .EntireColumn.Interior.ColorIndex = 35
    End With
End Sub

You need to paste this code into the sheet module where you want to use it. Right-click on the desired sheet and select View Code from the menu. When the sheet module opens, paste the code above. As a result, you will have something like in the photo

harzer · Oct 11, 2024

Hello Sergius,
Thank you for your feedback and your proposal.
I am glad that you understood my request because I was afraid of not explaining my request well.
However, your proposal colors the entire row and column of the active cell, but I am looking to color only three cells as indicated in the image, which will allow me to subsequently copy these three colored cells to another sheet.
Thank you again for your proposal.
Greetings.

Skyybot · Oct 13, 2024

Try this. This code should be placed in the Sheet code module of the Sheet you want to affect.

VBA Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, lCell
lCell = Me.UsedRange.Address
lCell = Right(lCell, Len(lCell) - InStr(1, lCell, ":"))
Set rng = Range("L2", lCell)
rng.Cells.Interior.Color = RGB(255, 255, 255)
Target.Interior.Color = RGB(0, 255, 0)
Me.Cells(Target.Row, "L").Interior.Color = RGB(0, 255, 0)
Me.Cells(2, Target.Column).Interior.Color = RGB(0, 255, 0)
End Sub

harzer · Oct 13, 2024

Hello Skyybot,
Thanks for your proposal.
It's beautiful, it's really what I wanted, Thank you very much for this beautiful work.
Greetings.

Color 3 cells.

harzer

Board Regular

Excel Facts

Sergius

Board Regular

harzer

Board Regular

Skyybot

Well-known Member

harzer

Board Regular

Similar threads

Forum statistics

Share this page

Color 3 cells.

harzer

Board Regular

Excel Facts

Sergius

Board Regular

harzer

Board Regular

Skyybot

Well-known Member

harzer

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock