Conditional formula

topi1

Active Member
Joined
Aug 6, 2014
Messages
256
Office Version
  1. 2010
I want conditional formula that will highlight a cell in the range C3:L12 in sheet4 that matches the following formula. Can someone please help? Thank you.

=INDEX(C3:L12, MATCH(Sheet3!D2, B3:B12, 0), MATCH(Sheet3!E2, C2:L2, 0))
 
Is this what you need?

Sheet3:
DANTE AMOR
ADEF
1
2R2H3
3
Sheet3

Sheet4:
DANTE AMOR
ABCDEFGHIJKLM
1
2H1H2H3H4H5H6H7H8H9H10
3R1
4R2
5R3
6R4
7R5
8R6
9R7
10R8
11R9
12R10
13
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:L12Expression=MATCH(Sheet3!$D$2, $B:$B, 0)&"|"&MATCH(Sheet3!$E$2, $2:$2,0)=ROW()&"|"&COLUMN()textNO


🧙‍♂️
 
Upvote 0
Solution
Is this what you need?

Sheet3:
DANTE AMOR
ADEF
1
2R2H3
3
Sheet3

Sheet4:
DANTE AMOR
ABCDEFGHIJKLM
1
2H1H2H3H4H5H6H7H8H9H10
3R1
4R2
5R3
6R4
7R5
8R6
9R7
10R8
11R9
12R10
13
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:L12Expression=MATCH(Sheet3!$D$2, $B:$B, 0)&"|"&MATCH(Sheet3!$E$2, $2:$2,0)=ROW()&"|"&COLUMN()textNO


🧙‍♂️
@DanteAmor Perfect. Thank you. Did not understand "stop if true" Part.
 
Upvote 0
Did not understand "stop if true" Part.

That is used when you have more rules, the rules are applied from the top down. For example, if you have 2 rules that apply to the same cell, but you want it to stop at the first format, then you activate the Stop if true box.
In this case it is not necessary to check the box.

😇
 
Upvote 0
That is used when you have more rules, the rules are applied from the top down. For example, if you have 2 rules that apply to the same cell, but you want it to stop at the first format, then you activate the Stop if true box.
In this case it is not necessary to check the box.

😇
Understood. Thanks.
 
Upvote 0
Unless column B and/or row 2 could have repeated values, you could also use this shorter option.

topi1.xlsm
ABCDEFGHIJKL
1
2H1H2H3H4H5H6H7H8H9H10
3R1
4R2
5R3
6R4
7R5
8R6
9R7
10R8
11R9
12R10
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:L12Expression=AND($B3=Sheet3!$D$2,C$2=Sheet3!$E$2)textNO
 
Upvote 0
Unless column B and/or row 2 could have repeated values, you could also use this shorter option.

topi1.xlsm
ABCDEFGHIJKL
1
2H1H2H3H4H5H6H7H8H9H10
3R1
4R2
5R3
6R4
7R5
8R6
9R7
10R8
11R9
12R10
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:L12Expression=AND($B3=Sheet3!$D$2,C$2=Sheet3!$E$2)textNO
@Peter_SSs Thank you.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top