CODEWITHSUNDEEP
excelconditional-formatting
user2842721
user2842721

Reputation: 125

Adding an Excel Data bar to one cell that is entirely based on the value of another cell

If I have a value, say 10 in cell A1 and a percentage in cell A2, say 40%. Is it possible for me to add a data bar to cell A1 (minimum -100%, max 100%) that is based on cell A2 being 40%, completely irrespective of cell A1 being 10?

I know I can set the min and max to look at another cell but I'm not sure about the actual data bar value.

Upvotes: 3

Views: 5666

Answers (2)

Ampersand
Ampersand

Reputation: 183

You can fake the databar formatting using Gradient and ColorStops.

Dim barValue as Double
barValue = Range("A2").Value
If barValue = 0 Then
    barValue = 0.000001
Else If barValue = 1 Then 
    barValue = 0.999998
End if

With Range("A1").Interior
    .Gradient.ColorStops.Clear
    With .Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorAccent1
    End With
    With .Gradient.ColorStops.Add(barValue)
        .ThemeColor = xlThemeColorAccent1
    End With
    With .Gradient.ColorStops.Add(barValue + 0.000001)
        .ThemeColor = xlThemeColorDark1
    End With
    With .Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorDark1
    End With
End With

Upvotes: 0

pnuts
pnuts

Reputation: 59440

Sticking my neck out, no, not with 'native' Conditional Formatting. For example, set up a data bar and copy that formatting to a bank cell - where it will have no effect (other than to override any formatting already present there). Next add a value into that cell - you should now see a data bar.

Min and max from different cells set the bounds but the rule needs a data point to determine the actual, rather than relative, size of the bar. That data point comes from the formatted cell and can't be driven from elsewhere.

Upvotes: 1

Related Questions