Apple Event: May 7th at 7 am PT
is there any way to set a cell to turn (e.g.) green when the adjacent cell contains a certain text?
i.e. A2='Work', therefore B2 cell Color Fills green. ??
OR A2='Creativity, therefore B2 cell Color Fills blue ??
MacBook Pro 13", macOS 10.14
Hi Cornfused,
you can do it with an extra column for each conditional highlighting rule. You can hide these columns when all is working.
Formula in C2 (and fill down) =IF($A2="Work",$B2,"")
Formula in D2 (and fill down) =IF($A2="Creativity",$B2,"")
Conditional highlighting
Regards,
Ian.
Hi Cornfused,
you can do it with an extra column for each conditional highlighting rule. You can hide these columns when all is working.
Formula in C2 (and fill down) =IF($A2="Work",$B2,"")
Formula in D2 (and fill down) =IF($A2="Creativity",$B2,"")
Conditional highlighting
Regards,
Ian.
Hmmm...It seemed to work when I tested the first formula with the conditional highlighting, but the color doesn't change when I choose a different category (e.g. Creativity, Wellness, etc.). Did I miss a step?
When I deleted the Green Fill rule, the cell changed to reflect the remaining Grey Fill rule. (pictured below)
*Note: I added timeslots to column A, so the formulas are referencing column B now. Just to avoid any confusion.
Hi cornfused,
Enter this formula in D2 and fill right and fill down
=IF($B2=D$1,$C2,"")
For this to work, you must have something entered in each body cell of column C.
I am now thinking about the Conditional Highlighting of column C, but it is late here.
Over to you and others!
Regards,
Ian.
UPDATE: Nevermind! I figured it out. Very small clerical error. 🤦🏾‍♀️
ARRRGH!!! I finally got it to work, but it isn't translating to ANY of the other rows! Maddening. I've copied the formulas, everything is exactly the same except for the rows. Even when I moved the properly working row from Row 2 to Row 10, it works, but no other row is functioning properly. What on earth am I missing??
Pictured below are the formulas in Row 10 vs Row 9:
Row 10
Row 9
"Next question is can I do the same thing for my Start Time and End Time columns?
"So far, it seems like I can't because the Conditional Highlighting doesn't offer an option for Dates to reference the F column."
Consider the Start and End times entered in D10 and E10.
Under what conditions would each of these cells be highlighted?
Yes! Select the cell you'd like, then click 'Format', then 'Cell'.
At the bottom, there is a button for 'Conditional Highlighting'. You can then select 'Add new rule, then 'Text', and use the options there to help you.
Hope that helps!
Next question is can I do the same thing for my Start Time and End Time columns? So far, it seems like I can't because the Conditional Highlighting doesn't offer an option for Dates to reference the F column.
Hi cornfused,
Now you have me "cornfused".
Why does every body cell in column C contain the same text?
What will you enter in Start Time and End Time given that you already have hours of the day in column A?
Regards,
Ian.
All rows in column F seem to be working correctly.
In rows where column B contains "Work", column F contains a copy of the data in column C.
In rows where column B contains other text, column F is 'blank'.
The exception in the last two rows occurs because column B is empty. Many functions will assign the numerical value zero when coming upon an empty cell, and you'll note that this happened in these rows of column F in your table. Conditional highlighting rules assessing text do not recognize "0" as being the same as "" (the empty string), so those cell are not highlighted.
Regards,
Barry
The cells in Column C have to have some data in it for the color conditioning to function. This has been my experience at least with this system. Also, I copied the same text in all the cells as a sample to help me see which rows weren't working properly. Then I was able to go in and fix (once I figured out what the issue). But, I've got that part of it working now.
The Start and End Time is the actual time logged. Column A serves as a fixed time column for scheduling.
Hope all that makes sense.
Thanks, Barry, I eventually figured it out. I just somehow botched the formulas in the rows where the cells weren't working.
What I'm still trying to figure out is how to apply the conditional highlighting to columns D & E? The cells formatted for date and time don't offer the same opportunities for conditional highlighting as column C, so I'm not sure how or if I can even do it.
"Why does every body cell in column C contain the same text?"
Hi Ian,
Every cell needs to contain some text to provide a check on whether the formula copying that text into one of the 'highlight key' columns is doing its job correctly. The "same" text is easier to fill into all cells than is putting different text into each.
For this venue, I would suggest using a much shorter text string and horizontally compressing the table to allow for a larger scale image on the screen.
Regards,
Barry
Hi cornfused,
Yes, I understand. However, I think that to add conditional highlighting to the Start and End times would require many, many combinations of (in Barry's words) 'highlight key' columns.
Regards,
Ian.
Hi Barry,
Thanks. Now I understand that the OP was just testing.
Regards,
Ian.
conditional highlighting?
