|
Worksheet_Calculate with no effect
Hi,
i have such a code: Private Sub Worksheet_Calculate() Dim rang As Range Application.ScreenUpdating = False Application.EnableEvents = False For Each rang In Range("P6:S6") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next For Each rang In Range("P10:S10") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next Application.ScreenUpdating = True Application.EnableEvents = True End Sub unfortunatelly, cells in ranges P6:S6 and P10:S10 are not being coloured and i have no idea why how can i check this |
Worksheet_Calculate with no effect
Dear God:
I pasted you code into a blank worksheet and tried typing values into the ranges. The colors did not respond until I entered =RAND() in another cell. This generated calculations and the colors began to respond. If you are not generating calculations, consider using the Change Event instead. -- Gary''s Student - gsnu200750 "God Itself" wrote: Hi, i have such a code: Private Sub Worksheet_Calculate() Dim rang As Range Application.ScreenUpdating = False Application.EnableEvents = False For Each rang In Range("P6:S6") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next For Each rang In Range("P10:S10") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next Application.ScreenUpdating = True Application.EnableEvents = True End Sub unfortunatelly, cells in ranges P6:S6 and P10:S10 are not being coloured and i have no idea why how can i check this |
Worksheet_Calculate with no effect
It works for me.
You do realise it is using the worksheet_Calculate event, so it doesn't change when you change those values. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "God Itself" wrote in message ... Hi, i have such a code: Private Sub Worksheet_Calculate() Dim rang As Range Application.ScreenUpdating = False Application.EnableEvents = False For Each rang In Range("P6:S6") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next For Each rang In Range("P10:S10") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next Application.ScreenUpdating = True Application.EnableEvents = True End Sub unfortunatelly, cells in ranges P6:S6 and P10:S10 are not being coloured and i have no idea why how can i check this |
Worksheet_Calculate with no effect
Hi,
this code should function when F9 is pressed (i have automatic calculation disabled) i should get this effect when i use Calculate in code, am i right? Użytkownik "Gary''s Student" napisał w wiadomości ... Dear God: I pasted you code into a blank worksheet and tried typing values into the ranges. The colors did not respond until I entered =RAND() in another cell. This generated calculations and the colors began to respond. If you are not generating calculations, consider using the Change Event instead. -- Gary''s Student - gsnu200750 |
Worksheet_Calculate with no effect
it does not change when F9 or caluculate is used (automatic caluclation is
disabled) i dunno why.. U¿ytkownik "Bob Phillips" napisa³ w wiadomo¶ci ... It works for me. You do realise it is using the worksheet_Calculate event, so it doesn't change when you change those values. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "God Itself" wrote in message ... Hi, i have such a code: Private Sub Worksheet_Calculate() Dim rang As Range Application.ScreenUpdating = False Application.EnableEvents = False For Each rang In Range("P6:S6") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next For Each rang In Range("P10:S10") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next Application.ScreenUpdating = True Application.EnableEvents = True End Sub unfortunatelly, cells in ranges P6:S6 and P10:S10 are not being coloured and i have no idea why how can i check this |
Worksheet_Calculate with no effect
CNTRL-ALT-F9
-- Gary''s Student - gsnu200750 "God Itself" wrote: Hi, this code should function when F9 is pressed (i have automatic calculation disabled) i should get this effect when i use Calculate in code, am i right? Użytkownik "Gary''s Student" napisał w wiadomości ... Dear God: I pasted you code into a blank worksheet and tried typing values into the ranges. The colors did not respond until I entered =RAND() in another cell. This generated calculations and the colors began to respond. If you are not generating calculations, consider using the Change Event instead. -- Gary''s Student - gsnu200750 |
Worksheet_Calculate with no effect
What is the calculation mode in ToolsOptionsCalculation?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "God Itself" wrote in message ... it does not change when F9 or caluculate is used (automatic caluclation is disabled) i dunno why.. U¿ytkownik "Bob Phillips" napisa³ w wiadomo¶ci ... It works for me. You do realise it is using the worksheet_Calculate event, so it doesn't change when you change those values. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "God Itself" wrote in message ... Hi, i have such a code: Private Sub Worksheet_Calculate() Dim rang As Range Application.ScreenUpdating = False Application.EnableEvents = False For Each rang In Range("P6:S6") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next For Each rang In Range("P10:S10") If rang.Value = "-" Then rang.Interior.ColorIndex = 2 rang.Font.ColorIndex = 2 ElseIf rang = -1 And rang < -0.8 Then rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 ElseIf rang = -0.4 And rang < -0.2 Then rang.Interior.ColorIndex = 33 ElseIf rang = -0.2 And rang < 0 Then rang.Interior.ColorIndex = 34 ElseIf rang = 0 And rang < 0.2 Then rang.Interior.ColorIndex = 40 ElseIf rang = 0.2 And rang < 0.4 Then rang.Interior.ColorIndex = 44 ElseIf rang = 0.4 And rang < 0.6 Then rang.Interior.ColorIndex = 45 ElseIf rang = 0.6 And rang < 0.8 Then rang.Interior.ColorIndex = 46 ElseIf rang = 0.8 And rang < 1 Then rang.Interior.ColorIndex = 53 End If If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If Next Application.ScreenUpdating = True Application.EnableEvents = True End Sub unfortunatelly, cells in ranges P6:S6 and P10:S10 are not being coloured and i have no idea why how can i check this |
Worksheet_Calculate with no effect
manual
U¿ytkownik "Bob Phillips" napisa³ w wiadomo¶ci ... What is the calculation mode in ToolsOptionsCalculation? |
Worksheet_Calculate with no effect
Also, look carefully at your list of If statements. You have a second If
statement right after the first one, which ignores all of the previous logic. For example: If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If ....resets the ColorIndex to 2 if the cell has a value between 0.6 and 1, even though it was set to 46 or 53 in the long If statement above. -- Regards, Bill Renaud |
Worksheet_Calculate with no effect
Hi,
first if statement concerns Interior.colorindex second one concerns Font.colorindex regards U¿ytkownik "Bill Renaud" napisa³ w wiadomo¶ci ... Also, look carefully at your list of If statements. You have a second If statement right after the first one, which ignores all of the previous logic. For example: If rang = -1 And rang < -0.6 Then rang.Font.ColorIndex = 2 ElseIf rang = -0.6 And rang < 0.6 Then rang.Font.ColorIndex = 1 ElseIf rang = 0.6 And rang < 1 Then rang.Font.ColorIndex = 2 End If ...resets the ColorIndex to 2 if the cell has a value between 0.6 and 1, even though it was set to 46 or 53 in the long If statement above. -- Regards, Bill Renaud |
Worksheet_Calculate with no effect
Your Calculate event handler will only be called if you have cell on the
worksheet that is a formula that depends on a value in Range("P6:S6") or Range("P10:S10"). Set a breakpoint at the first line of code and you will see that your code never gets called when you only change the value in one of these cells, if you have no dependent cells. You should be using the Change event handler, as Gary's Student mentioned: Private Sub Worksheet_Change(ByVal Target As Range) 'Your code here. End Sub -- Regards, Bill Renaud |
Worksheet_Calculate with no effect
<<first if statement concerns Interior.colorindex
second one concerns Font.colorindex Yes, you are correct. Sorry, I didn't catch that. -- Regards, Bill Renaud |
Worksheet_Calculate with no effect
You,re right, but values in Ranges P6:S6 and P10:S10 are results of formulas
used. i do not change them manually anyway, now i used Application.CalculateFull and cells get coloured... but.. i tested once more such a code and this Private Sub should work also with F9 (calculate) as well as with CTRL+ALT+F9 (application.calculatefull) regards U¿ytkownik "Bill Renaud" napisa³ w wiadomo¶ci . .. Your Calculate event handler will only be called if you have cell on the worksheet that is a formula that depends on a value in Range("P6:S6") or Range("P10:S10"). Set a breakpoint at the first line of code and you will see that your code never gets called when you only change the value in one of these cells, if you have no dependent cells. You should be using the Change event handler, as Gary's Student mentioned: Private Sub Worksheet_Change(ByVal Target As Range) 'Your code here. End Sub -- Regards, Bill Renaud |
Worksheet_Calculate with no effect
The excel won't recalc until you force it to. And if excel isn't recalculating,
then the _calculation event won't fire. God Itself wrote: manual U¿ytkownik "Bob Phillips" napisa³ w wiadomo¶ci ... What is the calculation mode in ToolsOptionsCalculation? -- Dave Peterson |
Worksheet_Calculate with no effect
Your code will only work when that particular worksheet is being calculated.
F9 only calculates the minimum set of sheets, cells and ranges that need calculating, this does not neccessarily include every sheet. Ctrl-Alt-F9 calculates everything even if it does not need to be calculated. regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "God Itself" wrote in message ... You,re right, but values in Ranges P6:S6 and P10:S10 are results of formulas used. i do not change them manually anyway, now i used Application.CalculateFull and cells get coloured... but.. i tested once more such a code and this Private Sub should work also with F9 (calculate) as well as with CTRL+ALT+F9 (application.calculatefull) regards U¿ytkownik "Bill Renaud" napisa³ w wiadomo¶ci . .. Your Calculate event handler will only be called if you have cell on the worksheet that is a formula that depends on a value in Range("P6:S6") or Range("P10:S10"). Set a breakpoint at the first line of code and you will see that your code never gets called when you only change the value in one of these cells, if you have no dependent cells. You should be using the Change event handler, as Gary's Student mentioned: Private Sub Worksheet_Change(ByVal Target As Range) 'Your code here. End Sub -- Regards, Bill Renaud |
Worksheet_Calculate with no effect
Not sure, but I think your two blocks of code are similar. Would this idea
help? For Each Rang In Range("P6:S6,P10:S10").Cells 'Your code listed here once. Next Rang -- Dana DeLouis <snip |
Worksheet_Calculate with no effect
ElseIf rang = -1 And rang < -0.8 Then
rang.Interior.ColorIndex = 11 ElseIf rang = -0.8 And rang < -0.6 Then rang.Interior.ColorIndex = 5 ElseIf rang = -0.6 And rang < -0.4 Then rang.Interior.ColorIndex = 41 etc.... In addition, you have a long list of IF Statements: One of a few options is to break the range of values into intervals. Once you've numbered your intervals, you then convert. I've used Mod for the conversion to your ColorIndex values. Here's one idea; Select Case Rang.Value Case Is < -1, Is = 1 'Do Nothing Case Is < 0 x = -10 * WorksheetFunction.Ceiling(Rang, -0.2) Rang.Interior.ColorIndex = 45689081 Mod (x + 39) Case 0 To 1 x = 10 * WorksheetFunction.Floor(Rang, 0.2) Rang.Interior.ColorIndex = 2715183 Mod (x + 47) End Select -- HTH :) Dana DeLouis Windows XP & Excel 2007 "Dana DeLouis" wrote in message ... Not sure, but I think your two blocks of code are similar. Would this idea help? For Each Rang In Range("P6:S6,P10:S10").Cells 'Your code listed here once. Next Rang -- Dana DeLouis <snip |
| All times are GMT +1. The time now is 12:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com