Forum Discussion

Copper Contributor

Jan 10, 2024

Solved

Not is blank formula not working

I am trying to conditionally format a spreadsheet currently using the NOT(ISBLANK) formula. I have used the same formula reading =NOT(ISBLANK($AN18:$AN26)) and this is working fine. The new fo...

excel

Formulas and Functions

HansVogelaar
Jan 12, 2024
MRobinson0297

So let's say the Target dates are in F55:K55, the Revised dates in F56:K56 and the Actual dates in F57:K57.

Select F55:K56 (2 rows by 6 columns). F55 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=F56<>""

Click Format...
Activate the Fill tab.
Select white as highlight color.
Click OK, then click OK again.

P.S. If you only want to color a cell white if it contains a date and the cell below it also contains a date, use the following formula instead:

=AND(F55:F56<>"")

HansVogelaar

MVP

Jan 10, 2024

MRobinson0297

Can you explain in words which range you want to format and what the rule is intended to do?

MRobinson0297
Copper Contributor
Jan 10, 2024
So I need the formula to read the row below it and make the cell white if there is something in the corresponding below cell.

For example if this was on cell A1 I want it to read B1.
A1 is currently green.
If there is a date in B1 I want A1 to go white.
- HansVogelaar
  MVP
  Jan 10, 2024
  MRobinson0297
  
  Let's say you want to apply this to F11:K11.
  
  Select this range. F11 should be the active cell in the selection.
  
  Create a conditional formatting rule of type "Use a formula to determine which cells to format", with formula
  
  =F12<>""
  
  This is the condition for the active cell F11. Excel will automatically adjust it for the other cells in the selection.
  - MRobinson0297
    Copper Contributor
    Jan 12, 2024
    Will this work for every cell to read the corresponding one below it or will they all just read the F11 cell?