SOLVED

Conditional Formatting based on two cells

Brass Contributor

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 attached so I need to conditionally format b1 and b2 so if i write in one of them the yellow color disappears. Same goes for b3 and b4 with the blue.

 

Many thanks,

 

Bailey100

 

13 Replies

@Bailey100 

 

Please attach the file.

Thanks

@tauqeeracma 

 

Hi,it won't allow me to upload the file.

 

Thanks, Bailey100

@Bailey100 

 

While replying, click on below icon to attach a file.

clipboard_image_0.png

@tauqeeracma 

 

It wont upload?

@tauqeeracma , uploading doesn't work now

@Bailey100 

 

First select the column or cells you want to apply conditional formatting then click on :

 

Conditional Formatting --> Highlight Cells Rules --> More Rules --> Format Only Cells With --> Blanks -- > Apply desired formatting.

 

clipboard_image_0.png

clipboard_image_1.png

@tauqeeracma 

 

This works for if I want to conditionally format one cell based on the value in that specific cell, however I want a conditional format that is based on two cells. So for example if there is a blank in either cell b1 or b2 then both those cells with remain red, however if i type in b1 OR b2 the red colour is removed in b1 AND b2. How do i do this please?

 

 

@Bailey100 

 

Yes you can do conditional formatting based on two cells. Please apply below in Cell B1:

clipboard_image_0.png

 

And this in B2

clipboard_image_0.png

Thanks

 

@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!

 

 

@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)

@Sergei Baklan 

 

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?

best response confirmed by Bailey100 (Brass Contributor)
Solution

@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.

1 best response

Accepted Solutions
best response confirmed by Bailey100 (Brass Contributor)
Solution

@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.

View solution in original post