Conditional format first non-blank cell in multiple rows

0
votes

Hopefully this will stop the chain.

See the linked questions if you want more background, but I need to conditional format multiple rows (2,000+) from the FIRST (leftmost) non-blank cell + the next 11 columns after it. Not sure if it's needed for the conditional format formula, but I am able to get the start cell for each row, can kind of get the end cell (see below).

Cell address of the first populated cell in the row (*the data starts on row 2, the values begin in column C and end in column P):

{=(ADDRESS(2,COLUMN(INDEX(C2:P2,MATCH(1,IF(C2:P2<>0,IF(C2:P2<>"",1)),0)))))}

^ this gets me an absoluted text-version of the leftmost populated cell in each row. I have all these addresses in a helper column. I am then able to get the desired stopping-point for the format (12th cell to the right of the cell returned from above formula), but I have to manually enter the cell address derived from above formula:

=ADDRESS(2,COLUMN(OFFSET(N2,0,11,1,1)))

I can't nest the start cell formula inside this second formula or it breaks.

THANK YOU!

Desired result (ignore the different colors, they can be the same):

enter image description here

1
excelexcel-formulaconditional-formattingvba
Why is the "rock" row not colored? – Ken
Sorry, this is just from a different question that is linked to this. But you are right, the Rock row would be highlighted as well. – Lorenzo

1 Answers

1
votes

I added a helper column C that finds the first non blank in the row (my data went from column D to column AZ)

=MATCH(TRUE,INDEX((D2:AZ2<>0),0),0)

My conditional format rule applied to D2 to AZ4 was to highlight when the following was true:

==AND(COLUMN(D2)<($C2+11+COLUMN($D2)),COLUMN(D2)>=$C2+COLUMN($C2))

You can modify this to put the helper column where you wish, and to use named ranges.

(Had to add condition to not start coloring before the first instance!)