highlight cell in column B on every first not empty cell in following columns

Question

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 :)

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.