Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Trying to change color of cell conditionally
I'm getting frustrated with this problem because I can't seem to find
any other questions that are similar enough to help me figure out what I need to do to get my VB to work. Essentially, I have a spreadsheet with (6) different ranges(C4:K4, C9:K9, I14:K14, C20:K20, C25:K25, H30:K30). I want to change the color of cells in those ranges depending on the percentage value in the cell - which is the result of a formula operation already occupying the cell [=IF(K4<K6,((K4/K6)-1),"")]. If the percentage value is .10 then I want the color of the cell to be changed to Index 36 (pastel red), and if the percentage value is < -.10 then I want to change the cell color to Index 34 (pastel green). I attempted to use conditional formatting to solve my problem, and it appeared to work - halfway - in that it would work until a new percentage value registered in another conditionally formatted cell. I.e. the cell C5 would be changed to green, until G5's percentage value changed, at which time C5 would revert to a default color of pastel red, and G5 would become conditionally formatted (taking on whatever color was defined). My need is two-fold. I'd like to know what the heck was up with the conditional formatting, and why it was being so funky; and secondly I need to know what VB I need to use to change cell colors based upon criteria in those six ranges I specified. Any assistance is very much appreciated! Currently I'm stuck with the following code: (attached to "Sheet1") Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, ActiveSheet.Range() Is Nothing Then Call Test End If End Sub (in Module1) Sub Main_NvsInstanceHook() ' ' Main_NvsInstanceHook ' This routine calls the appropriate InstanceHook routines ' Application.Run "NVSUSER.XLM!YTDDrill" End Sub Sub Test() Dim oCell, r1, r2, r3, r4, r5, r6, MyRange As Range For Each oCell In Range("C5:K5", "C10:K10") If oCell.Value < "" Then Select Case oCell.Value Case Is < "-0.1" oCell.Interior.ColorIndex = 34 Case Is "0.1" oCell.Interior.ColorIndex = 36 Case expr1 To expr2 oCell.Interior.ColorIndex = 40 End Select End If Next oCell End Sub Thanks for you help in advance! --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Trying to change color of cell conditionally
Abraham,
The VB code, goes in the worksheet code module Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True On Error GoTo ws_exit If Not Intersect(Target, Range("C4:K4,C9: K9,I14:K14,C20:K20,C25:K25,H30:K30")) Is Nothing Then Select Case Target.Value Case Is 0.1: Target.Interior.ColorIndex = 36 Case Is <= 0.1: Target.Interior.ColorIndex = 34 End Select End If ws_exit: Application.EnableEvents = True End Sub The conditional formatting problem sounds to me that used absolute references to me. I think that all your formulae check against $G$5, so they all reflect that cell's value. What you should do is select all these cells, and put the first cell in the formula (C4 in this case). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Trying to change color of cell conditionally
Thanks for the solution you provided. I added the code to Module1 an
removed everything else (I assume this would be correct). However while it doesn't create any errors, it also doesn't seem to be doin anything, and running it doesn't yield a response either... Also changing percentage values on the spreadsheet don't cause the effect I'd like to get from the code. I forgot on my initial post to include that I currently have Iteration (of 2) turned on in the sheet in order to use what would otherwise b circular reference errors. I've realized that regardless of conditional formatting if I enter value in say, C4 which causes a change in the percentage valu displayed in C5, and then move over to D4 and enter a new value, th moment I press enter the percentage value displaying in C5 disappears This completely mystifies me. My only thought is that perhaps th Iterations rule kicks in whenever a circular reference would occur which in practice is every time a value is changed in a cell such as C or D4, etc... And that when it kicks in it nullifies or undoes whateve it did before... Any more ideas? Do I need to take out the iterations, and if so how d I solve for circular references. The following are the formulas fo cells C4, C5, C6, and C7 to illustrate the mechanics I'm talkin about: In C4 (there's nothing, other than a user entered value, such a 24.50) In C5: =IF(C4<C6,((C4-C6)/C6),"") In C6: =IF(C6<C4, IF(C7=C6,C4,C6), C6) In C7: =IF(C4<C6,C6,C7) Essentially C7 stores the value previously in C4 (before a new value i entered into C4) in order so that I can determine the percentage o difference between the old value and the new value. Thanks again -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Trying to change color of cell conditionally
"abrahamvionas " wrote in message ... Thanks for the solution you provided. I added the code to Module1 and removed everything else (I assume this would be correct). However, while it doesn't create any errors, it also doesn't seem to be doing anything, and running it doesn't yield a response either... Also, changing percentage values on the spreadsheet don't cause the effects I'd like to get from the code. Abraham, It doesn't go in Module 1, it goes in the worksheet code module. To get here, right-click the sheet name tab, Select View Code from the menu, and paste the code in there. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Trying to change color of cell conditionally
I know I'm frustrating because I'm such a newbie with VB, sorry about
that. Thanks for your insightful assistance though. I suspect that the Iterations I have turned on for the worksheet are causing problems for the VB script. I suspect this because each time I move on to the next cell to put in a different value the percentage cell associated with the last cell I changed resets (i.e. goes back to containing NO value). However, without the Iterations being turned on my formulas don't work because they'd create circular references. And yet I don't have a clue about how to do the same thing I've managed to do with Iterations and Formulas in Excel, in VB. The psuedo code would go something like this: Upon a change in the value of AnyCell in Range("C4:K4","C9:K9") Do: Store previous value into X Divide value in X by new Value Display resulting percentage in CellChanged.Offset(1,0) -- i.e. right below the cell in which a change occured. Mostly the part I can't think of a way to do is making it be triggered by a change to a cells value, and then recognizing which cell was changed, and storing data in appropriate variables (which have an established association with the variable which has changed) - say the initial change occurs in C4, the procedure would need to identify that fact, and then store the following values in C6 and C7, and display the resulting percentage in C5. Again, sorry to be such a pain, I really appreciate your help! --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Trying to change color of cell conditionally
Abraham,
Why don't you post me the workbook. Post it to bob . phillips @ tiscali . co . uk remove the embedded spaces. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "abrahamvionas " wrote in message ... I know I'm frustrating because I'm such a newbie with VB, sorry about that. Thanks for your insightful assistance though. I suspect that the Iterations I have turned on for the worksheet are causing problems for the VB script. I suspect this because each time I move on to the next cell to put in a different value the percentage cell associated with the last cell I changed resets (i.e. goes back to containing NO value). However, without the Iterations being turned on my formulas don't work because they'd create circular references. And yet I don't have a clue about how to do the same thing I've managed to do with Iterations and Formulas in Excel, in VB. The psuedo code would go something like this: Upon a change in the value of AnyCell in Range("C4:K4","C9:K9") Do: Store previous value into X Divide value in X by new Value Display resulting percentage in CellChanged.Offset(1,0) -- i.e. right below the cell in which a change occured. Mostly the part I can't think of a way to do is making it be triggered by a change to a cells value, and then recognizing which cell was changed, and storing data in appropriate variables (which have an established association with the variable which has changed) - say the initial change occurs in C4, the procedure would need to identify that fact, and then store the following values in C6 and C7, and display the resulting percentage in C5. Again, sorry to be such a pain, I really appreciate your help! --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any method to set cell background color conditionally ? | Excel Worksheet Functions | |||
how can I conditionally change font color, or background color? | Excel Worksheet Functions | |||
Button or key to conditionally change text foreground color? | New Users to Excel | |||
Button or key to conditionally change text foreground color? | New Users to Excel | |||
Button or key to conditionally change text foreground color? | New Users to Excel |