Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

conditional highlighting?

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

Posted on Jan 19, 2020 2:55 PM

Reply
Question marked as Top-ranking reply

Posted on Jan 19, 2020 8:11 PM

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.

14 replies

Jan 19, 2020 11:56 PM in response to Yellowbox

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.

Jan 20, 2020 9:46 AM in response to Yellowbox

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


Jan 23, 2020 9:31 PM in response to Yellowbox

"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?

Jan 21, 2020 12:06 AM in response to cornfused

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

Jan 23, 2020 11:10 AM in response to Yellowbox

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.

Jan 23, 2020 11:14 AM in response to Barry

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.

Jan 23, 2020 9:36 PM in response to Yellowbox

"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

conditional highlighting?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.