CODEWITHSUNDEEP
excelformattingconditional-statementscell
Kelli Klinger
Kelli Klinger

Reputation: 1

Conditional formatting cells based on adjacent cell

I am trying to show that a cell must be filled in. I want to use conditional formatting to highlight the cell isn't filled out using conditional formatting. Based on an EMPTY spreadsheet (gets filled in weekly by row, so I don't want a bunch of red cells until a line is filled in). I want a cell in column I to turn red when either columns "B:H" have text. Then if the person fills out a cell in column I the coloring goes away. I have tried =AND(ISTEXT(B#),ISNONTEXT(I#)), but this means that when a different row in column B is filled out the conditional formatting doesn't work. I then tried to individually put the condition formatting in, but it only works on the first 2 rows. I also tried =AND(ISTEXT($B#),ISNONTEXT($I#)) to show that it is based on the column, that also didn't work. Any help would be greatly appreciated.

Upvotes: 0

Views: 161

Answers (1)

Isolated
Isolated

Reputation: 6454

Assuming B:H are empty. You can use an AND formula looking at COUNTA (of B:H) combined with ISBLANK (column I).

Select range I2:I4 and input this formula into your Conditional Format Rule:

=AND(COUNTA(B2:H2)>=1,ISBLANK(I2))

Then change your conditional format fill as RED.

Output:

enter image description here

Upvotes: 0

Related Questions