Condition formatting based on TRUE/FALSE output of another cell

Question

I have 2 tables:

1. containing values
2. containing TRUE/FALSE outputs based on a formula that depends on the values of Table 1

I would like to apply condition formatting so that Table 1 cells are color RED with corresponding FALSE outputs from Table 2.

Solution 1

For example, this is your data:

      A      B
1     3    FALSE
2     2    TRUE
3     7    FALSE
4     9    TRUE

So now you want to format the text color of cell A1 and A3 to be red.

So these steps that I have tried:

1. Put your cursor at A1
2. In Home tab, choose Conditional Formatting, then New Rule...
3. In New Formatting Rule choose Use a formula to determine which cell to format.
4. Write this formula in Format values where this formula is true : =B1=FALSE, then set the formatting as you want by clicking Format... button. Then click OK.
5. Now open again Conditional Formatting, then choose Manage Rules...
6. Then in Applies to change this =$A$1 to =$A$1:$A$4. Click button Apply then OK.
7. Now you can see text color in cell A1 and A3 are red.

Hope it is useful.

Solution 2

Your issue can be fixed using helper Column.

N.B.

• Since you have not provided any sample data so that I've assumed two set of data for Tables.

How it works:

• I'm assuming Table 1 has data in Range A2:B4 and Table 2 has D2:E6.

• Enter this Formula in F2 and fill it down.

  =AND(COUNTIF(B$2:B$4,E2)>0,D2>IFERROR(OFFSET(B$2,MATCH(E2,B$2:B$4,0)-1,-1),0))

• Select B2:B4, reach to Conditional Formatting then New Rule and enter this Formula =$F2 =FALSE.

• Apply an appropriate Format & finish with Ok.

Let me say that how the formula works:

• The COUNTIF()... > 0 part returns TRUE if the value in Column E occurs in B2:B4.

• In second part the MATCH() finds the position of the the data in Column E Match with B2:B4.

• The OFFFSET() finds the corresponding data in Column A.

• IFERROR() handles #N/A error.

• If D2 is not greater than the Data (Number) in Column A, Formula returns FALSE.

Adjust Cell references in the Formula as needed.

Comments

[BruceWayne]

Conditional Formatting should do it. What have you tried?

[Emma]

Yes I have with making my own rule but it didn't work for the whole table. I had to do it in separate sections.

[BruceWayne]

that doesn't really help us here. Can you please post what you've tried, what works and what doesn't?

[Rajesh Sinha]

@Emma, could you share sample data along with Formula you have used for TRUE/FALSE, help us to fix it in better way!

[Scott - Слава Україні]

You can skip steps 5 and 6 if, at step 1, you just select A1:A4.