Excel Color Scale based on another columns data

[HardBeatZ]

Hey everyone!

I would like to use conditional formatting (the color scale option) to highligh a column of data based on another columns data.

Here is how my data is set up

	Actual	Target
Person 1	64.3%	50.0%
Person 2	67.6%	50.0%
Person 3	63.1%	50.0%
Person 4	59.4%	50.0%
Person 5	66.5%	45.0%
Person 6	53.7%	50.0%
Person 7	37.5%	45.0%
Person 8	50.9%	40.0%
Person 9	50.4%	50.0%
Person 10	49.2%	50.0%

<tbody>
</tbody>

Basically I want to highligh column 2 based on how close the number is to what is in column 3. In this instance Person 9 should be the brightest green and person 5 should be the darkest red as person 9 is closest to their target and person 5 is the furtherst away from the target.

I tried a few things

1. I tried using another column and calculated the absolute difference. I then tried to set a style to use that format to apply to the second column.
2. For giggles I used the format paint feature to see if that would work and it also did not work.
3. I then set my helper column to not show values and only show the formats. Using the excel camera I was able to take an image of the format and overlay it on top of my other cells. Unfortunately, while this worked it did not let the numbers bleed through even though I set the fill and border of the image to none. (I'm also not sure if this will auto update as necessary).

I'm currently stumped and at a loss and about ready to just say it can't be done, but I figured I would get more eyes on it to see if anyone could provide any insight.

Thanks!

 

[mumps]

I have tried an approach that works but is limited by the number of people you are dealing with. You could give it a try and see how it works out. Let's assume that your data is in columns A, B and C starting in row 2. Format columns B, C and D as percentage. In cell D2 place this formula and copy it down: =ABS(B2-C2). Next sort your data on column D, smallest to largest. Then run this macro:

Sub test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim x As Long
    x = 344
    Dim rng As Range
    For Each rng In Range("B2:B" & LastRow)
        rng.Interior.Color = RGB(x, 255, 153)
        x = x - 20
    Next rng
    Application.ScreenUpdating = True
End Sub

You can then re-sort on the person's name. The macro decreases the red value in the RGB color by 20 for each loop. The problem is that the value of x cannot go below zero and this will depend on the number of people. You can play around with the numbers and see what happens.

 

[HardBeatZ]

Thank you for the answer. Unfortunately I wasn't wanting to do anything that requires any manual intervention as I'd like to be able to dump my data in and just let it update by itself.

What I've done to resolve the issue is to create a new hidden tab that duplicates the first tab information so I have all columns lined up. I then used the camera feature to stack the color scale image and an image of the results on top of each other and then overlayed it on my other tab. This allowed it to have the look I wanted although if you look close enough you'll see that the rows don't quite line up and are off by a hair.

This auto updates when new data is dumped in and doesn't require any action from me so I'm sold.

 

[mumps]

Glad it worked out.