Conditionally format cells in an array based on values in a separate array-CodePudding

I have array A (C5:G9) and array B (I5:M9). I want to conditionally format array A based on values in array B. If array B has the number 3 anywhere in its array, I want the corresponding cell in array A to be highlighted.

I've tried all kinds of if statements and I keep getting a circular reference error.

CodePudding user response：

Is this what you are looking for, if so then follow the steps as mentioned below

1.) First select the range A1:A10,

2.) Then from Home Tab --> Under Styles Group --> Click Conditional Formatting,

3.) Click New,

4.) From New Formatting Rule,

5.) Select --> Use A Formula To Determine Which Cells To Format,

6.) Enter the below formula in the Edit The Rule Description,

=VLOOKUP($A1,$AA$1:$AA$10,1,0)=$A1

7.) Click Format --> From Fill Tab --> Choose Desired Color --> Press Ok --> Ok

And Done!