Prev: Month & Year Text to Date Format - Help Please
Next: Line break from vLookup
From: ShadowedFlames on 5 Mar 2010 09:19
I'm working on a financial project for my employer, and I'm looking for a
different way to gradually change the cell color based on a percentage
instead of the arbitrary "default style + 3 conditions" type brought about by
Conditional Formatting. Please keep in mind that I've not worked with Visual
Basic at all, and haven't even touched the BASIC/QBASIC language in over a
decade. I'm also using Excel 2003 at work with the capability of reading a
2007 file format (which I use on my home computer, if the situation warrants
it).

What I'm aiming for is something akin to this: when the full amount of
funds is available for a project, I would like the background color to be set
to RGB (0, 255, 0). As funds are taken away, the percentage of funds
remaining would affect the G value so that as more funds are used, the cell
background color slowly starts to fade to black.

Examples:
84.7% remaining: 255 * .847 = 215.985 (truncated to 215). RGB = (0, 215, 0)
48.7% remaining: 255 * .487 = 124.185 (truncated to 124). RGB = (0, 124, 0)
11.1% remaining: 255 * .111 = 28.305 (truncated to 28). RGB = (0, 28, 0)
and so on....

Is there a way to effect this using VBA coding, or am I going to have to be
satisfied with the three-tier effect of Conditional formatting?
From: Bob Phillips on 5 Mar 2010 11:35
You don't have to have just 3 colours, but you are restricted to the colours
in the colour palette, all 40 of them, in Excel 2003


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case >.87: .Interior.ColorIndex = 6 'yellow
Case >.487: .Interior.ColorIndex = 5 'blue
Case >.111 .Interior.ColorIndex = 10 'green
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob

"ShadowedFlames" <ShadowedFlames(a)discussions.microsoft.com> wrote in message
news:6A37C7A3-3C3A-483B-A7C3-7A3D56D5AD7A(a)microsoft.com...
> I'm working on a financial project for my employer, and I'm looking for a
> different way to gradually change the cell color based on a percentage
> instead of the arbitrary "default style + 3 conditions" type brought about
> by
> Conditional Formatting. Please keep in mind that I've not worked with
> Visual
> Basic at all, and haven't even touched the BASIC/QBASIC language in over a
> decade. I'm also using Excel 2003 at work with the capability of reading
> a
> 2007 file format (which I use on my home computer, if the situation
> warrants
> it).
>
> What I'm aiming for is something akin to this: when the full amount of
> funds is available for a project, I would like the background color to be
> set
> to RGB (0, 255, 0). As funds are taken away, the percentage of funds
> remaining would affect the G value so that as more funds are used, the
> cell
> background color slowly starts to fade to black.
>
> Examples:
> 84.7% remaining: 255 * .847 = 215.985 (truncated to 215). RGB = (0, 215,
> 0)
> 48.7% remaining: 255 * .487 = 124.185 (truncated to 124). RGB = (0, 124,
> 0)
> 11.1% remaining: 255 * .111 = 28.305 (truncated to 28). RGB = (0, 28, 0)
> and so on....
>
> Is there a way to effect this using VBA coding, or am I going to have to
> be
> satisfied with the three-tier effect of Conditional formatting?


From: Monarch on 5 Mar 2010 11:37
Range("A2").Interior.Color = RGB(0,Round(255 * Cells(1, 1).Value, 0), 0)

where Range("A2") is affected cell
Cells(1,1) is cell that contains % (percentage value) in format
Cells(ROW, COLUMN).value, cells(3,1) is A3 and cells(1,3) is C1

On 05.03.2010 15:19, ShadowedFlames wrote:
> I'm working on a financial project for my employer, and I'm looking for a
> different way to gradually change the cell color based on a percentage
> instead of the arbitrary "default style + 3 conditions" type brought about by
> Conditional Formatting. Please keep in mind that I've not worked with Visual
> Basic at all, and haven't even touched the BASIC/QBASIC language in over a
> decade. I'm also using Excel 2003 at work with the capability of reading a
> 2007 file format (which I use on my home computer, if the situation warrants
> it).
>
> What I'm aiming for is something akin to this: when the full amount of
> funds is available for a project, I would like the background color to be set
> to RGB (0, 255, 0). As funds are taken away, the percentage of funds
> remaining would affect the G value so that as more funds are used, the cell
> background color slowly starts to fade to black.
>
> Examples:
> 84.7% remaining: 255 * .847 = 215.985 (truncated to 215). RGB = (0, 215, 0)
> 48.7% remaining: 255 * .487 = 124.185 (truncated to 124). RGB = (0, 124, 0)
> 11.1% remaining: 255 * .111 = 28.305 (truncated to 28). RGB = (0, 28, 0)
> and so on....
>
> Is there a way to effect this using VBA coding, or am I going to have to be
> satisfied with the three-tier effect of Conditional formatting?
From: ker_01 on 5 Mar 2010 16:29
I recently asked a very similar question; you might find the responses useful:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&tid=7329def8-03e1-4b10-a172-da1f114a2a40&cat=en_US_3a793e1f-4961-419d-9ec7-899d6e6086cd&lang=en&cr=US&sloc=&p=1

HTH,
Keith

"ShadowedFlames" wrote:

> I'm working on a financial project for my employer, and I'm looking for a
> different way to gradually change the cell color based on a percentage
> instead of the arbitrary "default style + 3 conditions" type brought about by
> Conditional Formatting. Please keep in mind that I've not worked with Visual
> Basic at all, and haven't even touched the BASIC/QBASIC language in over a
> decade. I'm also using Excel 2003 at work with the capability of reading a
> 2007 file format (which I use on my home computer, if the situation warrants
> it).
>
> What I'm aiming for is something akin to this: when the full amount of
> funds is available for a project, I would like the background color to be set
> to RGB (0, 255, 0). As funds are taken away, the percentage of funds
> remaining would affect the G value so that as more funds are used, the cell
> background color slowly starts to fade to black.
>
> Examples:
> 84.7% remaining: 255 * .847 = 215.985 (truncated to 215). RGB = (0, 215, 0)
> 48.7% remaining: 255 * .487 = 124.185 (truncated to 124). RGB = (0, 124, 0)
> 11.1% remaining: 255 * .111 = 28.305 (truncated to 28). RGB = (0, 28, 0)
> and so on....
>
> Is there a way to effect this using VBA coding, or am I going to have to be
> satisfied with the three-tier effect of Conditional formatting?
From: ShadowedFlames on 20 Mar 2010 14:40
Thank you all for your help. I'll be giving these a try when I return to my
office this week. (I would have replied sooner but for some reason I never
got the "reply notification" e-mails I specifically said I wanted for this
topic. Hmm.)

In any event, I will try these out and see what works best for me. Thanks
again!
 | 
Pages: 1
Prev: Month & Year Text to Date Format - Help Please
Next: Line break from vLookup