Conditional formatting using arrays

[Sharksfan]

I have 2 arrays that are 6 x 6. I would like to conditional format the cells in the first array based on whether the corresponding values in the second array are less than 15% (in green) or greater than or equal to 15% (in red). Below is an example of the 2 arrays. The first row is column labels in each array The first array is located at H2:M8. The second array is located at V2:AA8. A wrinkle, but not required, would be if the percentage is 0% in array 2, then the corresponding cells in array 1 would remain un-formatted. I have searched the web and haven't struck gold, short of creating 2 conditional formatting rules for each cell in the first array based on the corresponding value in the second array (72 rules!)

Thank you,

Bob

[TABLE="width: 696"]
<tbody>[TR]
[TD] FCP1[/TD]
[TD] FCP1-2[/TD]
[TD] FCP1-3[/TD]
[TD] FCP1-4[/TD]
[TD] FCP1-5[/TD]
[TD] FCP1-6[/TD]
[/TR]
[TR]
[TD="align: right"]$94,045.69[/TD]
[TD="align: right"]$191,278.70[/TD]
[TD="align: right"]$319,858.32[/TD]
[TD="align: right"]$432,699.87[/TD]
[TD="align: right"]$558,319.13[/TD]
[TD="align: right"]$666,303.19[/TD]
[/TR]
[TR]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]$27,230.28[/TD]
[TD="align: right"]$57,828.63[/TD]
[TD="align: right"]$91,347.81[/TD]
[TD="align: right"]$123,487.03[/TD]
[TD="align: right"]$156,320.82[/TD]
[TD="align: right"]$189,571.23[/TD]
[/TR]
[TR]
[TD="align: right"]$11,569.16[/TD]
[TD="align: right"]$22,314.54[/TD]
[TD="align: right"]$35,165.74[/TD]
[TD="align: right"]$48,522.11[/TD]
[TD="align: right"]$61,103.96[/TD]
[TD="align: right"]$73,925.00[/TD]
[/TR]
[TR]
[TD="align: right"]$198,074.58[/TD]
[TD="align: right"]$248,835.31[/TD]
[TD="align: right"]$637,667.82[/TD]
[TD="align: right"]$872,446.90[/TD]
[TD="align: right"]$1,112,233.06[/TD]
[TD="align: right"]$1,340,813.76[/TD]
[/TR]
[TR]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 510"]
<tbody>[TR]
[TD]FCP1[/TD]
[TD]FCP1-2[/TD]
[TD]FCP1-3[/TD]
[TD]FCP1-4[/TD]
[TD]FCP1-5[/TD]
[TD]FCP1-6[/TD]
[/TR]
[TR]
[TD]1.1%[/TD]
[TD]1.5%[/TD]
[TD]4.0%[/TD]
[TD]8.8%[/TD]
[TD]14.4%[/TD]
[TD]20.3%[/TD]
[/TR]
[TR]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]2.0%[/TD]
[TD]3.9%[/TD]
[TD]9.4%[/TD]
[TD]15.6%[/TD]
[TD]23.1%[/TD]
[TD]30.0%[/TD]
[/TR]
[TR]
[TD]8.9%[/TD]
[TD]11.3%[/TD]
[TD]17.8%[/TD]
[TD]25.7%[/TD]
[TD]32.6%[/TD]
[TD]38.7%[/TD]
[/TR]
[TR]
[TD]2.4%[/TD]
[TD]3.7%[/TD]
[TD]4.2%[/TD]
[TD]8.1%[/TD]
[TD]13.5%[/TD]
[TD]19.1%[/TD]
[/TR]
[TR]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 512"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Welcome to the board.

Select H3:M8. Click Conditional Formatting > New Rule > Use a formula > and enter

=V3<15%

select a green fill color. Repeat the process with

=V3>=15%

and a red fill color. It's a matter of selecting the right range, and creating the formula based on the upper left cell in the range. Excel will adapt the formula for the rest of the range. Let us know how it works.

Edit: To not format cells with a zero value, use this formula for green:

=AND(V3<15%,V3<>0)

The red formula doesn't need changing.

 

[Sharksfan]

This worked! A coworker and I struggled for hours yesterday with this. Thank you!

Bob

Welcome to the board.

Select H3:M8. Click Conditional Formatting > New Rule > Use a formula > and enter

=V3<15%

select a green fill color. Repeat the process with

=V3>=15%

and a red fill color. It's a matter of selecting the right range, and creating the formula based on the upper left cell in the range. Excel will adapt the formula for the rest of the range. Let us know how it works.

Edit: To not format cells with a zero value, use this formula for green:

=AND(V3<15%,V3<>0)

The red formula doesn't need changing.

 

[Eric W]

Conditional Formatting is very useful, but sometimes it takes a while to wrap your head around how it works.

Glad to help!