Conditional Formatting with Merged Cells

[WaveWalker116]

TLDR: See attached spreadsheet, I want the cells in the chart to highlight as they fill in the data up top.

------------

Hello,

I'm trying to do some conditional formatting based off criteria in other cells. See attached, but the basic gist is, as the user fills in data in yellow cells up top, the cells in the chart below highlight green.

Right now, I have the first 2 "batches" of conditional formatting working. When end user selects Data 1 (please ignore blanks in dropdown, they're not there in real version), those cells highlight. When user selects Data 2, that row highlights. All this is good and working correctly.

Now what I'm trying to make happen is, for Columns H and I, I want them to highlight when Cell D6 is filled in. This is where things get confusing.

1) the cells are merged because the data in them takes up that hole space (i.e., the data in those cells applies to all of Data 1), so I can't continue the same conditional formatting found in C13 - G24 (unless you know a way!) because that conditional formatting is based on the row and it doesn't know to highlight all the merged cell.

2) Column H and I should only be highlighted AFTER Data 2 is entered, but the criteria for them to be highlighted is based of Data 1. In other words, the rows / merged cell in Column "H" and "I" should be highlighted because Data 1 is chosen, but only highlight after Data 2 is chosen. If it makes it easier, Columns H and I *could* be highlighted off Data 2, as the criteria going down the C column ("Data 2") never repeats.

3) and the one I most stuck on, I only want the merged cells to highlight for the number they're read across from. In other words, I don't want all of Column "H" and "I" to highlight, only that portion that's associated with Data 1.

I'm sure that sounds confusing, but the attached spreadsheet should clear it up.

Really, I just want the green highlight to appear as they fill in the data, which will make sense when looking at the example attached.

I'll have other questions for the cells to the right of this, but I wanted to get this one solved and then move on to the others to avoid confusion.

Thanks in advance!