Worksheet_Calculate with no effect

G

God Itself

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
 
G

Gary''s Student

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.
 
B

Bob Phillips

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)
 
G

God Itself

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" <[email protected]>
napisał w wiadomości
news:[email protected]...
 
G

God Itself

it does not change when F9 or caluculate is used (automatic caluclation is
disabled)

i dunno why..
 
G

Gary''s Student

CNTRL-ALT-F9
--
Gary''s Student - gsnu200750


God Itself said:
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" <[email protected]>
napisał w wiadomości
Click to expand...
 
B

Bob Phillips

What is the calculation mode in Tools>Options>Calculation?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
B

Bill Renaud

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.
 
G

God Itself

Hi,

first if statement concerns Interior.colorindex
second one concerns Font.colorindex

regards
 
B

Bill Renaud

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
 
B

Bill Renaud

<<first if statement concerns Interior.colorindex
second one concerns Font.colorindex>>

Yes, you are correct. Sorry, I didn't catch that.
 
G

God Itself

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
 
D

Dave Peterson

The excel won't recalc until you force it to. And if excel isn't recalculating,
then the _calculation event won't fire.
 
C

Charles Williams

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
 
D

Dana DeLouis

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
 
D

Dana DeLouis

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....
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine columns from seperate sheets into new sheet- macro improve 4
Help Shorten Execution Time of VBA program 2
Macro Hogging Memory - what to do? 0
Modify working code to expand the output. 5
conditional cell.value statement 2
Putting the series 1 and series 2 titles from a worksheet 0
Convert list to UPPER, lower & Proper cases. 21
Combine Two Different Worksheet_Change codes 9

Top