I'm trying to apply conditions formatting to cells that are not blank. Currently, I'm using 3 difference formulas to apply the conditional format to the columns (B, C and D).
Is it possible to use a single formula in column F to apply the conditional to the three columns?
I did not realize until Barry pointed out there was builtin rule for Cell is not blank in Conditional Formatting (CF). So, I was using formulas in columns F-H (=IF(B2≠"",CHAR(10003),"")) to create a ✓ if a given cell was not blank. I then use a CF rule Text does not contain to highlight the not blank cell. Instead of having rules in three different columns, I was trying to a single rule that would apply cells in columns B-D that were not blank.
Obviously, the builtin CF rule is what I should have been using.
I did not realize until Barry pointed out there was builtin rule for Cell is not blank in Conditional Formatting (CF). So, I was using formulas in columns F-H (=IF(B2≠"",CHAR(10003),"")) to create a ✓ if a given cell was not blank. I then use a CF rule Text does not contain to highlight the not blank cell. Instead of having rules in three different columns, I was trying to a single rule that would apply cells in columns B-D that were not blank.
Obviously, the builtin CF rule is what I should have been using.
Does this answer your question?
Example 1: All of the selected cells have the Conditional Highlighting rule shown to the right of the table.
The checkmark in column E is a text character, inserted into the cell. Values in columns B, C and D are entered from the keyboard. Other cells in these four columns are empty.
Column F contains two cells whose data format has been set to 'checkbox'. Both are highlighted because a checkbox cell always contains a value, either 'true' or 'false', and is never 'blank'.
In the second example, there is no change in the data format of the cells, but there is a change in the rule for the selected cells in column F. The new rule is shown to the right of the table. It is set to highlight the cells that are checked (ie. contain 'true') and ignore any other value in these cells.
Regards,
Barry
JessA1965 wrote:
Is it possible to use a single formula in column F
Not sure I understand what you mean. By "formula" do you mean Conditional Highlighting rule? Are you trying to have a cell in B:D highlight if you check a checkbox?
SG
Barry,
I did not realize there was builtin rule for Cell is not blank in Conditional Formatting (CF). I called myself looking through all the CF builtin rules, but overlooked this feature.
Thank you for your help.
Hi Jess,
Thanks for the green checkmark.
Also, thanks for the note that you did learn of a feature on reading a reply to your question here. Always nice to hear when someone learns something new!
Regards,
Barry
Conditional Format to Highlight Cells with an Entry (non-blank)
