CODEWITHSUNDEEP
excelformattingvba
Dan
Dan

Reputation: 13

Colour scale based on how far value is from other cell value

I have a column of targets (C2:C30) and a column of projections (D2:D30).

I've been able to conditionally format the projections to highlight values either above or below target, but I want to expand this into a colour scale to be able to identify more quickly how far from the target my projection is.

For example, if D:D is above 90% of C:C, a green colouring would suffice. 80-90% would be amber, below 80% would be red. In an ideal world, a fully fledged colour scale would be great but at this moment in time I'll be happy with anything more sophisticated than my current above/below setup.

I believe I could use a series of stacked conditional formats but thought there might be a more efficient way of doing this?

Upvotes: 0

Views: 3071

Answers (1)

PeterH
PeterH

Reputation: 1040

You can create your own scale,

Create first rule using something like:

=C2*1.9<D2

Apply this in Cell D2, then apply to D2:D30,

Then create second rule:

=C2*1.8<D2

And so on..

Upvotes: 1

Related Questions