EXCEL highlight corresponding rows and columns in other tables

Question

I have 3 tables in the same sheet as follows:

I need to highlight corresponding rows and columns in other 2 tables. What I can do now is to highlight rows and columns, but can not figure out how to highlight corresponding columns in other tables. For example, if I select Melon or one of its values in Table 1, then get same selection in other 2 tables, and vice versa.

I have used this formula in conditional formatting --> Use a formula to determine which cells to format:

=OR(CELL("col")=COLUMN();CELL("row")=ROW())

I can also use this VBA code to update automatically (otherwise press F9) to update highlight.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

Here's my result for now:

Any ideas? Thanks in advance!

I believe you will need VBA to make all tables sensitive to the active cell alone. You could create a drop down list with your items to select from and then create your rules around that cells value. That will only help you highlight the correct rows though. — urdearboy
Also, is Table1 the only table where the selection matters? If you select Lime in Table2 do you also want Lime to be highlighted in Table2 and Table3? If so, that requires more work — urdearboy
yes, when I select lime in first table, same row and column should be selected in other 2 tables. — Zaur
then only that tables rows and columns will be highlighted. But in my case, all 3 tables have same data in description field. They are just found in different rows. — Zaur

JvdV JvdV · Accepted Answer · 2020-12-28T16:30:10

You can try the following:

The conditional formatting rule for I3:O14:

=OR(ROW()-2=MATCH(INDEX($B:$B,CELL("row")),$J$3:$J$14,0),CELL("col")=COLUMN()-8)

Obviously you can apply the same logic to the other table. Note, this is only sensitive to selections in table1

EXCEL highlight corresponding rows and columns in other tables

1 Answers