conditional formatting help needed

[paramnayak]

Dear all,

I have an "m x n" table containing numbers,

If I select a number from this table , I want the top of row and column of the number to be highlighted. How can we do this using conditional formatting.

 

[xlstime]

Use below formula in condition formatting

row
= INDEX($A$1:$A$5,SUMPRODUCT(($A$1:$E$5=C9)*ROW($A$1:$E$5)))
col
=INDEX($A$1:$E$1,,SUMPRODUCT(($A$1:$E$5=E9)*COLUMN($A$1:$E$5)))

Dear all,

I have an "m x n" table containing numbers,

If I select a number from this table , I want the top of row and column of the number to be highlighted. How can we do this using conditional formatting.

View attachment 33410

 

[xlstime]

and for mid (numbers)

just simple
=c9=b2

here c9 is the location of your drop down and b2 is starting point of matrix (values)

Use below formula in condition formatting

row
= INDEX($A$1:$A$5,SUMPRODUCT(($A$1:$E$5=C9)*ROW($A$1:$E$5)))
col
=INDEX($A$1:$E$1,,SUMPRODUCT(($A$1:$E$5=E9)*COLUMN($A$1:$E$5)))

 

[bosco_yip]

Try........

1] Select B1:E1 >> Conditional Formatting >> new rule >> choose Use a Formula, enter :

=COUNTIF(B$2:B$5,$D$9)>0

>> Format >> Format Cell fill >> Choose "red" color >> OK

2] Select A1:A5 >> Conditional Formatting >> new rule >> choose Use a Formula, enter :

=COUNTIF($B2:$E2,$D$9)>0

>> Format >> Format Cell fill >> Choose "red" color >> OK

3] Select B2:E5 >> Conditional Formatting >> new rule >> choose Format only cell that contain >> choose : "Cell value" "equal to" "=$D$9"

>> Format >> Format Cell fill >> Choose "red" color >> OK

4] See attached file.

Regards
Bosco

 

[xlstime]

Very nice Bosco!!!..

Try........

1] Select B1:E1 >> Conditional Formatting >> new rule >> choose Use a Formula, enter :

=COUNTIF(B$2:B$5,$D$9)>0

>> Format >> Format Cell fill >> Choose "red" color >> OK

2] Select A1:A5 >> Conditional Formatting >> new rule >> choose Use a Formula, enter :

=COUNTIF($B2:$E2,$D$9)>0

>> Format >> Format Cell fill >> Choose "red" color >> OK

3] Select B2:E5 >> Conditional Formatting >> new rule >> choose Format only cell that contain >> choose : "Cell value" "equal to" "=$D$9"

>> Format >> Format Cell fill >> Choose "red" color >> OK

4] See attached file.

Regards
Bosco