Excel 2003 "Databar" help

[wongs18]

Hi Guys!

I found this amazing vba code online:
Function DataBar(lngValue As Long)
DataBar = String(lngValue, ChrW(9608))
End Function

Basically allows me to generate these "bars" to represent my data.
However; it doesn't generate bars for negative data...
I was wondering if it is possible to modify this code so if the data is positive the bar grows to the right as green and if it is negative the bar grows to the left red?

Thanks gusy!

 

[Andrew Poulsom]

Sorry, you can't change the colour of text in a cell with a function.

 

[wwbwb]

I got this to work for positve green data.

Function DataBar(lngValue As Long)
DataBar = String(lngValue, ChrW(9608))
If lngValue > 0 Then ActiveCell.Font.Color = -11489280
End Function

of course... this only works if the bar is the activecell...

 

[wwbwb]

Ok, try this. I changed a few things. It has two variables. The first is the value or location of the value, the second is the location of the function itself. Negative doesn't go to the left, but it colors it.

Function DataBar(lngValue As Long, myloc As Range)
DataBar = String(Abs(lngValue), ChrW(9608))
If lngValue > 0 Then
myloc.Font.Color = -11489280
Else
myloc.Font.Color = -16776961
End If
End Function

 

[wongs18]

Nope doesn't work ;(

 

[wwbwb]

How are you setting it up?
A1: 5
B1: =databar(A1,B1)

That should get you a green bar 5 long.

A1: -4
That should get you a red bar 4 long.

 

[wongs18]

The databar works but the colour is black for both positive and negative :'(

 

[wwbwb]

Try this. And depending on how you have it set up, you can right justify the negative values to make the bar appear it is going left.

Function DataBar(lngValue As Long, myloc As Range)
DataBar = String(Abs(lngValue), ChrW(9608))
If lngValue > 0 Then
myloc.Font.Color = RGB(34, 139, 34)
Else
myloc.Font.Color = RGB(255, 0, 0)
End If
End Function

 

[wongs18]

thank you so much for all your help! I really appreicate this! but I have no idea why... but it still doesn't show colour.. its still black ( could it be im using excel 2003?

 

[wwbwb]

I've got 2010 and it changes colors on mine. Tried it on a 2003 machine and it sticks with one color, so it might be. Worth a shot though.