Colorize a few cells by choosing a specific date (VBA or Conditional formatting)

[Schturman]

Hi
How I can do this ?
For example, if I click / choose a date 11/04 in a range D2:J7, it should colorize the same date (and descriptions in 2 near cells) and same color in a range L2:N15 if the same date exist in column L.
Is it possible by VBA or Conditional formatting?
Thanks

 

[DanteAmor]

Hi @Schturman. Thanks for posting on the forum.

We need to put the format conditions and a little code in VBA to make it work automatically.

Use the following 2 conditional formats:

varios 12abr2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				D	E	F	G	H	I	J		L	M	N
2				26-mar	27-mar	28-mar	29-mar	30-mar	31-mar	01-abr		05-abr	sd	sd
3				02-abr	03-abr	04-abr	05-abr	06-abr	07-abr	08-abr		06-abr	sd	sd
4				09-abr	10-abr	11-abr	12-abr	13-abr	14-abr	15-abr		07-abr	sd	sd
5				16-abr	17-abr	18-abr	19-abr	20-abr	21-abr	22-abr		08-abr	sd	sd
6				23-abr	24-abr	25-abr	26-abr	27-abr	28-abr	29-abr		09-abr	sd	sd
7				30-abr	01-may	02-may	03-may	04-may	05-may	06-may		10-abr	sd	sd
8												11-abr	sd	sd
9												12-abr	sd	sd
10												18-abr	sd	sd
11												25-abr	sd	sd
12												25-abr	sd	sd
Hoja5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:N12	Expression	=$L2=INDIRECT(CELL("address"))	text	NO
D2:J7	Expression	=ADDRESS(ROW(),COLUMN())=CELL("address")	text	NO

In the sheet events, put the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

NOTE SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


Example:

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Schturman]

Hi @Schturman. Thanks for posting on the forum.

We need to put the format conditions and a little code in VBA to make it work automatically.

Use the following 2 conditional formats:

varios 12abr2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				D	E	F	G	H	I	J		L	M	N
2				26-mar	27-mar	28-mar	29-mar	30-mar	31-mar	01-abr		05-abr	sd	sd
3				02-abr	03-abr	04-abr	05-abr	06-abr	07-abr	08-abr		06-abr	sd	sd
4				09-abr	10-abr	11-abr	12-abr	13-abr	14-abr	15-abr		07-abr	sd	sd
5				16-abr	17-abr	18-abr	19-abr	20-abr	21-abr	22-abr		08-abr	sd	sd
6				23-abr	24-abr	25-abr	26-abr	27-abr	28-abr	29-abr		09-abr	sd	sd
7				30-abr	01-may	02-may	03-may	04-may	05-may	06-may		10-abr	sd	sd
8												11-abr	sd	sd
9												12-abr	sd	sd
10												18-abr	sd	sd
11												25-abr	sd	sd
12												25-abr	sd	sd
Hoja5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L2:N12	Expression	=$L2=INDIRECT(CELL("address"))	text	NO
D2:J7	Expression	=ADDRESS(ROW(),COLUMN())=CELL("address")	text	NO

In the sheet events, put the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

NOTE SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.


Example:
View attachment 89524


--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Wow, thank you very much !!! It working perfectly !!!
I changed a little bit a conditional formatting and used only one line, because I don't want to colorize the dates in a range D2:J7, because it's already have colors related to the holidays and I wanted to use the same colors in a range L2:N15.
I used it like this, related to the holidays (religions, national etc..)

Thank you !