Forum Discussion
Can you conditional format a cell based on the color of other cells?
Can you conditionally format (color a cell) based off of the conditional formatting (color) of another cell in an array? For example. In column A I have samples A3:A100 I subject Sample A3 to a ...
A conditional formatting rule cannot refer to the conditional formatting of other cells, but it can use the conditions used for those cells.
If you want to color cell A3 red if any of the cells in B3:Z3 is smaller than B2 or larger than B1:
- Select A3.
- On the Home tab of the ribbon, select Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format.
- Enter the formula
=COUNTIF(B3:Z3,">"&B1)+COUNTIF(B3:Z3,"<"&B2)
- Click Format...
- Activate the Fill tab.
- Select red.
- Click OK, then click OK again.
Thanks, let me clarify. Each test B3:Z3 will have their independent Lower and Upper Limits as they are all separate tests, each limits will have different values so I can't have B3:Z3 only refer to B1 and B2.
- B3 has limits B1 and B2
- There are different limits for C3 (C1 and C2). D3 limits (D1 and D2) and so forth. All tests B to Z have independent limits.
So I'm looking to turn A3 Red if it fails ANY of the B3:Z3 tests. aka, it passes all tests except may fail at Y3 because Y3 values is outside Y1 and Y2.
- B3 has limits B1 and B2
- There are different limits for C3 (C1 and C2). D3 limits (D1 and D2) and so forth. All tests B to Z have independent limits.
So I'm looking to turn A3 Red if it fails ANY of the B3:Z3 tests. aka, it passes all tests except may fail at Y3 because Y3 values is outside Y1 and Y2.
If you don't have a recent version of Excel in Microsoft 365, you can use the following formula in the Conditional Formatting rule:
=SUMPRODUCT((B3:Z3>B1:Z1)+(B3:Z3<B2:Z2))