Fill in empty cell if no value...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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?
 
Upvote 0
Correct.

Apologies if not too clear.

Regards,
Albert
 
Upvote 0
No problem.

Does it only need to apply to row 13 and 14?
 
Upvote 0
Yes as i can't have one without the other. Say to produce something you need to consume something and vice versa.
 
Upvote 0
Albert,
Select B13:B14 and apply the following formula in conditional formatting....

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

Hope that helps.
 
Upvote 0
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
 
Upvote 0
In conditional formatting rules manager:

edit rule as you say to remove the $.

Then change applies to, to:

=$b$13:$h$14
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top