Excel Conditional Formatting Formula Help

RumSwift · July 17, 2020

Hey,

Wondering if any Excel wizards can help me out here.

I'm working on a sheet for a project and I need to set certain cells to highlight when their value is higher than one of two numbers in a data set depending on the current selection of a drop down box.

To explain it a little better:

I have data in row C from cell 2 through to 26 and they're a percentage between 0% and 100%.
I then have 2 sets of fixed data. For example; Set One is 50% and Set Two is 30%.
I have a validated cell showing either "Set One" or "Set Two" selected by a drop down.

What I need to happen is; If the drop down is set to "Set One" I need the data in C2:C26 to highlight green only if it's greater than 50%. If the drop down is set to "Set Two" I need the data C2:C26 to highlight green only if it's greater than 30%.

The percentage for Set One and Set Two could change though, so I can't do it based off a static value, I'll need use use the cell value.

I tried setting up conditional formatting for "Highlight Cell Rule > Greater than" and using this formula in the "Greater than" option:

=IF(O3="Set One", $L$3, $L$4)

This didn't seem to work unfortunately.

Any ideas on how this could be achieved? I've made a mock sheet as well (It's a work project, can't post the actual sheet/data for obvious reasons) to try and visualize it as sometimes I can be bad at explaining things:

Spoiler

IntMD · July 17, 2020

My excel is pretty rusty for stuff like this, but i think you want something like this in the conditional formatting as the formula, and you'll need to do another rule for Set 2 but pretty much the same deal but different for the set two relative cells and name, where the C2:C35 is the range of your values so you could probably do the whole column if needed.

=IF(AND($O$3="Set One"),((C2:C35)>$L$3))

RumSwift · July 18, 2020

16 hours ago, IntMD said:

My excel is pretty rusty for stuff like this, but i think you want something like this in the conditional formatting as the formula, and you'll need to do another rule for Set 2 but pretty much the same deal but different for the set two relative cells and name, where the C2:C35 is the range of your values so you could probably do the whole column if needed.

=IF(AND($O$3="Set One"),((C2:C35)>$L$3))

Sorry for the late reply.

This works perfectly! Saved my weekend!