Excel Conditional Formatting Formula Help

[RumSwift]

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]

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]

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!

[IntMD]

No worries... Glad it works, had to dig that from the depths of my mind while I was bored at work waiting for Friday to end