I am trying to conditionally format cells so that they are shaded in green if they are higher than the value of D6 (the topmost cell pictured) and red if they are lower. In order to do this, the midpoint in my color scale seems like it should be D6, but this does not register as an appropriate value when I try it.
I know I could just input the value of D6 as the midpoint, but there are several sets of cells that I need to do this to and I would rather be able to paste the conditional formatting than set each conditional format up individually.
You need to set two rules in CF.
For green
=D7>$D$6S
For red
=D7<$D$6