Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Jun 29, 2025
Solved

Conditional formatting for an Array

Dear Experts,                      I have an Array 2D, in J3:L5, and in O3:Q5, I put a formula , to fetch the elements based on the Rows and Columns we set in O2 and P2 respectively, I want to high...
excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Jun 30, 2025

    When doing conditional formatting on an array (the array is defined in the Applies to range) you set the equation based on the upper left corner of the Applies to range and Excel will use the relative / absolute definitions in the equation to either increment or not each reference. So in your case it is simply:

    Formula: = J3=O3

    As Excel increments through the Applies to range of J3:L5 that equation will also increment the corresponding rows and columns.

    a common example of this is pretend the Date is in column A and you want to highlight all ROWS with a Date that has passed so the Applies to range might be A1:Z100 and the equation would be:

    =$A1<TODAY()

    so on every row every cell will check the date in column A because of the $ but as excel goes down the rows it will increment the row (because there is no $) so each row will get highlighted based on the date in column A.

    Hope that helps  

Kidd_Ip's avatar
Kidd_Ip
MVP
Jun 29, 2025

Take this:

 

  1. Select the range J3:L5.
  2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter this formula (assuming you're starting from cell J3):
=J3=INDEX($O$3:$Q$5,ROW(J3)-ROW($J$3)+1,COLUMN(J3)-COLUMN($J$3)+1)

 

4. Set your desired formatting (e.g., fill color) and click OK.

 

 

 

 

Resources