Conditional formatting if, and etc...

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 2016
Hi,

I have 2 columns, A & B.

Currently in A is no data and the same in B.

Should data be added to column A, then column B with highlight in red (fill). Then, when Column B has data added, this will then override the red fill with no fill (blank / white).

I have tried and failed...
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Select column B.
Then enter this CF formula:
=AND(A1<>"",B1="")
and choose your red fill formatting option.

This should do what you want.
 
Upvote 0
PERFECT, thank you so much.

How do i copy the formula in to numerous cells so that it works for the other cells also? Column A and B, if I drag it down it remains for the original cell.
 
Upvote 0
Notice my first step:
Select column B.
I didn't say select one cell, but rather the WHOLE column.
If you do that, it will apply that logic to every single cell in column B.

If you want to apply it to only a certain number of rows, select that exact range before entering the formula.

Some people like to use the Format Painter, but I have found that it often doesn't work the way you like or expect.
I find it much easier to do the following:
- Select ALL the cells you want to apply this Conditional Formatting to
- Enter the CF formula as it applies to the very FIRST cell in your selected range
Provided you use Absolute/Relative cell referencing correctly, it will automatically adjust the formula for the other cells in your selection.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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