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!

[Harribone]

You will also want to look into dynamic lists - the example attached allows you to select 2 and E but I'm guessing you want the drop down list for 'Data 2' to only show options related to the 'Data 1' value.

Anyways attached example with conditional formatting applied to Col H. Left Col I so you can have a go yourself using H as an example.
Select a cell in H and choose Conditional Formatting>Manage Rules to see what has been applied.

Hopefully this is what you are after.

[Glenn Kennedy]

is this it???


=$B13=$C$6

apply to $h$13 to$i$24

[WaveWalker116]

That's very close Glenn!

Only thing is, I would like to have those cells highlight AFTER Data 2 (cell D6) is chosen.

Maybe an IF statement?

Thanks for your help!!!

[WaveWalker116]

You will also want to look into dynamic lists - the example attached allows you to select 2 and E but I'm guessing you want the drop down list for 'Data 2' to only show options related to the 'Data 1' value.

Anyways attached example with conditional formatting applied to Col H. Left Col I so you can have a go yourself using H as an example.
Select a cell in H and choose Conditional Formatting>Manage Rules to see what has been applied.

Hopefully this is what you are after.

That what I'm looking for! And Yes, I do have dynamic lists where Data 2 goes blank if Data 1 changes (VBA)... but I appreciate the extra tip!

Thanks!!

[Glenn Kennedy]

No.. a ISNUMBEr-MATCH formula:

=AND($B13=$C$6,ISNUMBER(MATCH($D$6,$C13:$C18,0)))

[Harribone]

That what I'm looking for! And Yes, I do have dynamic lists where Data 2 goes blank if Data 1 changes (VBA)... but I appreciate the extra tip!

Thanks!!

By dynamic list i was referring to the available choices within the second drop down. Once drop down 1 is populated drop down 2 should only show options that match up with drop down 1. Just to enhance the experience and minimise errors.

Just something to consider anyway. Good luck with the rest of your project.