Forum Discussion
highlighting cells when text not entered
Hi, i have a sheet where i need cells with missing information to be highlighted.
The sheet has headings and if all relevant info is not input into relevant cells for each heading, then i have cell A1 show "LOG INCOMPLETE".
The code is =IF(COUNTA(B2:R2)>0,IF(COUNTA(B2:R2)=16,"",LOG INCOMPLETE"),"")
As i have column A formatted to show "LOG INCOMPLETE", I have set it so if there is no info in any cells on the next rows "LOG INCOMPLETE" doesn't show. I have done this as i don't want it to show until the relevant row is being completed (see code above).
However, i would like the cells with the missing info to be highlighted red (on the row that is being completed). I have used Conditional Formatting "=IF(A2="LOG INCOMPLETE",TRUE,FALSE)"which is great if its just one cell with missing info. When there are multiple cells that have missing info, i would like them all to be highlighted red, then when info entered, the formatting to clear, but the other cells (with missing info) to stay red until info entered.
I have tried several formulas and conditional formatting, but i just can't get it to clear the formatting once data is entered into the cell.
I have attached an example, so hopefully you will understand what i want.
Hopefully someone will be able to help as i am "stuck"!!
Thanks
PR
Hi Philip,
You may apply the rule with the formula
=ISBLANK(B2)*(COUNTA($B2:$R2)>0)
to your entire range. The only I'd unmerge C:D and remove column D if you don't use it.
Attached.
5 Replies
Hi Philip,
You may apply the rule with the formula
=ISBLANK(B2)*(COUNTA($B2:$R2)>0)
to your entire range. The only I'd unmerge C:D and remove column D if you don't use it.
Attached.
Hi Sergei
ive tried this but keep getting an error?
Do i use this in with the conditional formatting or in the cells direct?
Thanks
Phil
Philip, that's in conditional formatting as in the file I attached previous time, please check the rules in it.
