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 series of tests and the numeric results are in Cells B3:Z3 for the respective test. I put lower and upper limits of the tests above results respective rows. B3 has upper limit in B1 and lower limit in B2, upper limit C1 and lower limit C2 for C3, etc. Now I can do a reference conditional format for rows B3:Z3 to make the Row 3 cell Red if its outside the limits, (above B1 or below B2 become RED) and copy that for all samples and tests. How do I format A3 to be Red based on any failure from tests B3:Z3?
4 Replies
It would have been useful to have a sample workbook rather than having to reconstruct a version from your description. I read the description in exactly the same way as HansVogelaar . For me, using Excel 365, it make little difference other than adjusting the bounds UBound and LBound to be arrays rather than scalars. The Name 'violated' refers to
= LET( sample, XLOOKUP(@ID, ID, Samples), UBoundViolated, sample>UBound, LBoundViolated, sample<LBound, OR(UBoundViolated, LBoundViolated) )and provides the basis for a conditional format.
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.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))
