Trying to constrain gradient color scale rule to ignore coloring below 0.0 and above 1.0 on a row of sequential numbers. When I assign, say RED to minimum 0 and GREEN to maximum 1, the gradient still spills to cell values beyond these "limits". How do I change it up?


| -0.60 | 0.02 | 0.47 | 1.00 | 1.53 | 2.06 | ...x472 colunms later...

Here, only columns BCD would get gradient as they are 0 < x < 1.

My values I'm working with are always sequential, usually evenly spread, the sweetspot can show up anywhere on the row, and this target zone can be more than 3 columns wide. Thus why a great way to spot where in the world... thinking - like a heat map?

Also, 0, 0.5, 1.0 may not appear on that row, so closest without exceeding.

Here's a link to another example. From support.office.com (I know I know).
Highlight patterns example Just to visualise. The upper table highlights content of interest in pink, while "ignoring" 46 and lower. I want that highlight set to be gradient.

Google-savvy yes (a skill), but still unable to find the answer. Perhaps you can help, please?

  • 1
    You will probably need vba in a worksheet change event to set the range to apply the gradient. Gradient is based on the whole not part of the range to which it is applied. – Scott Craner Dec 3 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.