Forum Discussion

Brass Contributor

Jul 31, 2019

Solved

Conditional Formatting based on two cells

Hi, I'm making a document in which I need to be able to conditionally format two cells based on whether one of them is not blank (so it has been filled in with something). Here is a document ...

excel

Formulas and Functions

SergeiBaklan
Jul 31, 2019
Bailey100

You may apply the rule to B1:B2 only and, if you need another colour for the pair B3:B4, create one more rule applying to B3:B4 with similar formula, only change B1 on B3 in it. Or, alternatively, create the rule for B1:B2, after that apply by Format Painter to B3:B4 and change colour for this pair rule.

Bailey100

Brass Contributor

Jul 31, 2019

tauqeeracma

Hi, this works for blanks in both but if you input a number or text into one of the cells the format does not clear, until you have wrote in both. Do you know how to solve this,

Thanks!

SergeiBaklan

Diamond Contributor

Jul 31, 2019

Bailey100

You may use one rule with the formula

=LEN(B1&OFFSET(B1,MOD(ROW(),2)*2-1,0))=0

applied to entire column B (starting from cell B1)

SergeiBaklan
Diamond Contributor
Jul 31, 2019
Bailey100

You may apply the rule to B1:B2 only and, if you need another colour for the pair B3:B4, create one more rule applying to B3:B4 with similar formula, only change B1 on B3 in it. Or, alternatively, create the rule for B1:B2, after that apply by Format Painter to B3:B4 and change colour for this pair rule.
Bailey100
Brass Contributor
Jul 31, 2019
SergeiBaklan

Thanks, is it not possible to do it for selected sells such as maybe b1 and b2, as i need a separate one for b3 and b4?