Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to gradually change cell interior colors...?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to gradually change cell interior colors...?
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to gradually change cell interior colors...?
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to gradually change cell interior colors...?
I recently asked a very similar question; you might find the responses useful:
http://www.microsoft.com/communities...r=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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to gradually change cell interior colors...?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Interior Cell Color | Excel Programming | |||
Dynamic interior colors | Excel Programming | |||
Change the interior color of a cell - Code Review | Excel Programming | |||
Code to change interior colour only if current interior colour is | Excel Programming |