Conditional Formatting using Address(Row(),COL,4)

[RMac0001]

Hi there,
I am trying to do conditional formatting based on a value using ADDRESS(ROW(),COL(),4)
For the below example I want to conditionally format C5 to C16, D5 to D16 and so on if they are greater or equal to the values in C23 to C34, D23 to D34 and so on.

I am using A2 as variable 1 and the Values in column B as variable 2 as I have multiple target tables. So A2 could be Chat, Talk, Web.

The formula I am using in conditional formatting is =INDEX(C23:C100,MATCH(A2 & " " & INDIRECT(ADDRESS(ROW(),2,4)),$A$23:$A$100,0))>=INDIRECT("C"&ROW())

 

[Peter_SSs]

Welcome to the MrExcel board!

For the furure: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if you can adapt this smaller example (assuming that I have understood correctly)
If it doesn't work, please advise if those "-" values in the top section are zeros formatted as "-" or simply text values.

23 01 19.xlsm
	A	B	C	D
1
2	Chat
3
4
5			18.9	14.3
6			0.85	53.7
7			54.5	1.1
8
9
10
11
12
13
14
15
16
17
22
23	Chat November		0.86	4.25
24	Chat December		0.86	3.76
25	Chat January		3.44	2.45
26
27
36
37	Talk November		20	10
38	Talk December		0.2	60
39	Talk January		15	0.5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:F16	Expression	=AND(C5<>0,C5>=INDEX(C$23:C$100,MATCH($A$2&"*",$A$23:$A$100,0)+ROWS(C$5:C5)-1))	text	NO

If I change A2 to Talk:

23 01 19.xlsm
	A	B	C	D
1
2	Talk
3
4
5			18.9	14.3
6			0.85	53.7
7			54.5	1.1
8
9
10
11
12
13
14
15
16
17
22
23	Chat November		0.86	4.25
24	Chat December		0.86	3.76
25	Chat January		3.44	2.45
26
27
36
37	Talk November		20	10
38	Talk December		0.2	60
39	Talk January		15	0.5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:F16	Expression	=AND(C5<>0,C5>=INDEX(C$23:C$100,MATCH($A$2&"*",$A$23:$A$100,0)+ROWS(C$5:C5)-1))	text	NO

 

[RMac0001]

Welcome to the MrExcel board!

For the furure: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if you can adapt this smaller example (assuming that I have understood correctly)
If it doesn't work, please advise if those "-" values in the top section are zeros formatted as "-" or simply text values.

23 01 19.xlsm
	A	B	C	D
1
2	Chat
3
4
5			18.9	14.3
6			0.85	53.7
7			54.5	1.1
8
9
10
11
12
13
14
15
16
17
22
23	Chat November		0.86	4.25
24	Chat December		0.86	3.76
25	Chat January		3.44	2.45
26
27
36
37	Talk November		20	10
38	Talk December		0.2	60
39	Talk January		15	0.5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:F16	Expression	=AND(C5<>0,C5>=INDEX(C$23:C$100,MATCH($A$2&"*",$A$23:$A$100,0)+ROWS(C$5:C5)-1))	text	NO

If I change A2 to Talk:

23 01 19.xlsm
	A	B	C	D
1
2	Talk
3
4
5			18.9	14.3
6			0.85	53.7
7			54.5	1.1
8
9
10
11
12
13
14
15
16
17
22
23	Chat November		0.86	4.25
24	Chat December		0.86	3.76
25	Chat January		3.44	2.45
26
27
36
37	Talk November		20	10
38	Talk December		0.2	60
39	Talk January		15	0.5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:F16	Expression	=AND(C5<>0,C5>=INDEX(C$23:C$100,MATCH($A$2&"*",$A$23:$A$100,0)+ROWS(C$5:C5)-1))	text	NO

That works amazing. Thank you so much for you help. Also, I will make sure to use XL2BB in the future.
Thank you again

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.