Forum Discussion
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 highlight the elements in the array in J3:L5, which are equal to the ones in O3:Q5 when we set the values in O2,P2;
I used something like this, but it didn't worked , could you please share what to modify here? Attached is the worksheet.
Br,
Anupam
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
4 Replies
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
Thanks m_tarler​ for the concept.. Excel surprises me everyday... !
Take this:
- Select the range J3:L5.
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- 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.
Thanks Kidd_Ip​ for the solution.
