I'm working on a medicine sheet, where I want to highlight the times when a pill is added to one of the following columns. Right now it looks like this:


Each pill works for (about) 7 hours, so I take one at 6:30, at 7:30, at 9:30.
I've got everything I wanted in this sheet working how I want, except for this last bit. I want those times in column B highlighted.

I've tried working with INDEX and MATCH, even tried a whole workaround with a second sheet.... I can't figure it out.

EDIT: per request of Rubèn for more info (apologies for not doing so in the first place), I've decided to add a copy of my sheet: meds copy.

Apart from Column A on the meds sheet, basically everything is a result of searching dozens of sites for hours. For finding a way to highlight only one cell when there's more cells with data in them, then finding a way to get the position of the first cell with data in a column and so on and so on until I got where I am now.

Last night I managed to get it almost the way I want it, but as it is now, I have to apply conditional formatting cell by cell.

on the second sheet I check for the first cell with data in a column, resulting in a true/false. I then have that 'converted' to text in column A. on the first sheet I go cell by cell in column B to check if the corresponding cell on the second sheet equals "Found". If yes, it highlights.

I just can't imagine there isn't an easier way to do it.

Thanks in advance for any help :)


1 Answer 1


For the range B2:B, use this conditional formatting custom formula rule:

=arrayformula( sum( isblank(C1:Z1) * not(isblank(C2:Z2)) ) )

...or, more concisely:

=sumproduct( isblank(C1:Z1); not(isblank(C2:Z2)) )

These formulas work stand-alone and do not require a helper sheet. See your sample spreadsheet.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.