= OR ($A1 = row value, A$1 = Column value)
Explanation – What is Conditional Formatting?
Using conditional formatting, highlighting intersecting row(s) and column(s) with exact matching is easy to carry out. You can do this using the ‘OR’ function which is a formula based on mixed references.
Figure 1: Highlighting Intersecting Rows and Columns with Conditional Formatting
In the above example, the formula used in applying conditional formatting is:
=OR ($B8 = $J$6,E$5 = $J$7)
Conditional Formatting Formula – How it Works
Evaluating Conditional formatting is based on the range of cell(s) it covers, and it usually begins from active cells within the array of cells selected. In this scenario, cell B8 (for row) and E5 (for the column).’
To select identical rows, the expression below can be used :
Since you are beginning with the row, keep it unlocked while the column remains locked such that values in column B alone, are likened to the car in cell ‘B8’ by this, reference to cell ‘J6’ is mixed. On the other way round, the reference to ‘J6’ is absolute since this will avoid alterations when applying conditional formatting to every other cell within B5:G10. In the above example, this expression will cause ‘TRUE’ to be displayed for cells in any row where the car is ‘Benz’
- To select identical columns, the logical expression stated below will be very useful:
Again, reference to E5 is mixed, however this time, around the row is sealed and the column-free such that values in row 4 alone can be compared with the day value in cell ‘J7’. Note that the reference to cell ‘J7’ is complete such that it won’t change conditional formatting is operational on the array of cells in B5:G10. In the above example, this logical expression will display ‘TRUE’ for each cell in a column where row 4 is “Wednesday”.
You can now use the ‘OR’ function to wrap the above logical expression for both the column and the rows since you are applying identical conditional formatting (blue fill) to both the column and the row. Where one or both logical expressions display ‘TRUE’, the rule is activated and conditional formatting is made operational
- Selecting Intersection Only
Simply replace the ‘OR’ function with the ‘AND’ function to select intersection only
If you want to select the interconnecting rows and columns within selected data set B5:G10 using formula, apply the conditional formatting formula. Where either or both logical expressions in both the row and columns displays ‘TRUE’, the rule is activated and conditional formatting is applied.