I'm trying to conditional format cells in a row that are between two cells that have specific values.
I have found a conditional formatting custom formula using the 'COUNTA' function which formats all the cells UP TO (ie to the left of) a cell with a value, and only filling empty cells:
=AND(H19="",COUNTA(H19:19)>0)
...but is there a custom formula that formats the cells to the right of a specific value AND to the left of a specific value?
For example, in the screenshot below, the light grey fill goes up to the last cell with a value (the last 'OS'), but I want to specify that cells only get a fill if they are between 'DD' and 'DD', and 'OS' and 'OS', and NOT between the two ranges (where I've crossed out in yellow marker):
=AND(A19 ="", COLUMN(A19)>MATCH("DD",19:19,0),COLUMN(A19)<MATCH("DD",19:19,1))+AND(A19 ="", COLUMN(A19)>MATCH("SS",19:19,0),COLUMN(A19)<MATCH("SS",19:19,1))
For starters I wanna highlight that custom formula is not really good with iterative memorization or marking things out since it deals 1 cell at a time as a work around I play around the limitation of Match Function depending on type of search it will show the first occurrence or last occurrence which I mark as the basis of the occurrence. Also the markings have to be manually inputted. Moreover this formula doesn't tackle the possibility of an interlaced marker I.E DD SS DD SS. In these scenarios it will just mark everything in between them. It is just going to do exactly what's on your screenshot if there is any other variation of your dataset this formula might not work.
Reference: