Fill in empty cell if no value...

[albertc30]

Hi all.

I am having some trouble doing something, in my honest opinion, very simple.

Example;
Say B13 has a value, then if B14 is empty I want to fill this in red and if B13 is empty but B14 has a value than fill in red B13.

I am using the following for B13
=AND($B$14=0) "Red"

and for B14
=AND($B$13=0)

My problem now is that if both B13 and B14 are empty I get them filled in red. I do not want this.

How do I go about doing this pleasae?

I think I need to go back to basics.

Regards,
Albert

 

[CROY1985]

So just to clarify:

If both B13 and B14 are empty >>> Do nothing
If 1 isn't empty >>> highlight the other cell
If both aren't empty >>> do nothing

Is that correct?

 

[albertc30]

Correct.

Apologies if not too clear.

Regards,
Albert

 

[CROY1985]

No problem.

Does it only need to apply to row 13 and 14?

 

[albertc30]

ADVERTISEMENT

Yes as i can't have one without the other. Say to produce something you need to consume something and vice versa.

 

[Snakehips]

Albert,
Select B13:B14 and apply the following formula in conditional formatting....

=AND($B13="",COUNTA($B$13:$B$14)=1)

Hope that helps.

 

[CROY1985]

ADVERTISEMENT

as per Snakehips excellent suggestion^

 

[albertc30]

Albert,
Select B13:B14 and apply the following formula in conditional formatting....

=AND($B13="",COUNTA($B$13:$B$14)=1)

Hope that helps.

Snakehips;

Never seen such code, great and it works.

Only problem I am having now is that I need to apply this from to B13:H13 / B14:H14.

I have removed the $ so that i could do a drag along formula but when I do this the formula no longer works.

Sorry.

Many thanks for helping.

Albert

 

[CROY1985]

In conditional formatting rules manager:

edit rule as you say to remove the $.

Then change applies to, to:

=$b$13:$h$14

 

[albertc30]

Hi.

Done as you have sugested but it does not work.

=AND($B13="",COUNTA($B$13:$H$14)=1)

The problem here is that if I have a value is B13 then B14 comes up red if empty. But as is now a value in B13 and a value in C13 clears the red in the empty cell B14.

Sorry.

Many thanks.

Albert

P.S; I can always do the code for each individual set of two cells I ghuess.