Conditional number formatting

[ScorchedLemonade]

I've got the following situation: a list of 3 columns

x1 y1 z1
x2 y2 z2
x3 y3 z3
... ... ...
x10 y10 z10

The x1-x10, y1-y10, z1-z10 are pulled from a larger set of lists via formulae like:

=MIN(INDIRECT("B4:B"&$B$1-4))
=IF(COUNTIF(INDIRECT("B4:B"&$B$1-4),I5)>COUNTIF(I$4:I5,I5),I5,MIN(IF(INDIRECT("B4:B"&$B$1-4)<=I5,10000000,INDIRECT("B4:B"&$B$1-4)))) (xs)
=INDEX($A:$A,MATCH(I5,$B:$B,0)) (ys)
and
=IF(RIGHT(J5,1)="=",1/INDEX(S!$J:$J,MATCH(J5,S!$A:$A,0)),INDEX(S!$J:$J,MATCH(J5,S!$A:$A,0))) (zs)

Anyway, the main point is that the ys are just regular text and the zs are numbers.
They are linked to the xs which are a list of numbers also from a larger list (the bottom 10 of that list) and which are continously liable to change.

I'm wanting the zs to be formatted either as NumberFormat 0.00 or 0.0000 depending on the text in the corresponding ys.

I can conditionally format the zs to change colour if the ys change (and the number format is not appropriate) but not to change the number format itself.

Thus I can define a name NF referring to
=GET.CELL(7,INDIRECT("rc",FALSE))

and then set up conditions like

=and(right(RC[-1],1)="#",NF="0.00")

to turn the cell blue if y ends with # and the number format is 0.00 with other formats if say it doesn't end in # and the number format is 0.0000

Unfortunately, I can't then use the following to correct my number formatting because the changing of the colours doesn't trigger the macro (and because though the values of the xs, ys and zs change depending on the list they link to the actual formula doesn't - so that doesn't fire the macro either).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng
With Target
Set rng = Application.Intersect(Target, Range("J5:J14"))
If Not rng Is Nothing Then
If Right(Cells(.Row), 1) = "=" Then
Cells(.Row, "K").NumberFormat = "0.0000"
Else
Cells(.Row, "K").NumberFormat = "0.00"
End If
End If
Set rng = Application.Intersect(Target, Range("M5:M14"))
If Not rng Is Nothing Then
If Right(Cells(.Row), 1) = "#" Then
Cells(.Row, "N").NumberFormat = "0.0000"
Else
Cells(.Row, "N").NumberFormat = "0.00"
End If
End If
End With
End Sub

Is it possible to do this sort of thing, preferably in a way that isn't too exhaustive on resources as the file is already pretty intensive as it is?

Thanks.

 

[SkipVought]

Try looking a the Worksheet_Calculate event.

HOWEVER, look carefully at the number of times the Calculate event fires whenever a change occurs.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ScorchedLemonade]

I tried that but it didn't do anything from the automatic update. If I manually wrote over the cell, then it did change - but that was also true with Worksheet_Change.

Private Sub Worksheet_Calculate(ByVal Target As Range)
For i = 5 To 14
If Target.Address = "$J$" & i Then
If Right(Target.Value, 1) = "#" Then
Cells(i, "K").NumberFormat = "0.0000"
Else
Cells(i, "K").NumberFormat = "0.00"
End If
End If
Next i
End Sub

I don't really know what you mean by the last sentence? How can I know how many time the event fires?
Thanks, anyway.

 

[SkipVought]

I'd put a break in my code, insert a counter and watch what happens.

Did any calculation take place? On what sheet?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ScorchedLemonade]

I tried this: with a break point at "Next i" (I also added the a=1/0 later as it didn't break.)
If I type over the cells in J then the worksheet executes and I get the format change (but it doesn't add one to the counter cell and it doesn't break and neither does it complain about the a=1/0).
If I just let the sheet change naturally without forcing anything then the formats don't change.

Private Sub Worksheet_Calculate(ByVal Target As Range)
For i = 5 To 14
If Target.Address = "$J$" & i Then
If Right(Target.Value, 1) = "#" Then
Cells(i, "K").NumberFormat = "0.0000"
Else
Cells(i, "K").NumberFormat = "0.00"
End If
Cells(16, 10).Value = Cells(16, 10).Value + 1
End If
a = 1 / 0
Next i
End Sub

 

[SkipVought]

Break on the FIRST statement!

Do you have Calculate on?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ScorchedLemonade]

Calculate is set to Automatic.

I did find one big mistake though: although I put the Worksheet_Change in the right place (and this is the part that is executing when I type over) I put the Worksheet_Calculate in the wrong place embarrassingly.

Having fixed that massive error I now get:

Compile error:
Procedure declaration does not match description of event or procedure having the same name

It won't let up with this error message so I guess it is trying to execute fairly regularly.

I've no idea what that's about. In the "help" it talks about class modules but, as far as I'm aware there aren't any class modules. I don't know what a class module is - I certainly haven't inserted any myself. I just used ByVal Target As Range so I'm not sure why that would conflict.

 

[SkipVought]

There is no TARGET in Worksheet_Calculate.

Private Sub Worksheet_Calculate()

End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ScorchedLemonade]

Thanks - it does work now.
The counter has flown up - even after I set calculation to manual at the start of the sub and then to automatic at the end - and I'm afraid my sheet is pretty much crippled. Nevertheless I am thankful for your help - I know it can be done now.

Thanks again!

 

[SkipVought]

I don't have time to think it thru, but what you need to do is LOCK OUT events once the FIRST calculate event fires, using Application.EnableEvents = FALSE, then run the procedure, then turn events back on to finish.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Chats]

This may be too simple and understanding of your problem, but as an alternative to using VBA, could you hide the z's column (lets call it column C on the spreadsheet), then in column D put a formula along the lines of:

=IF(B2="a",TEXT(C2,"#.##"),TEXT(C2,"#.###"))

Nested IF's would allow more alternatives in the Y's column.

Any use?

 

[ScorchedLemonade]

That's better yet. Rather than hide the columns, I've moved them to another part of the sheet (as I have other stuff in those columns) and then referenced them as you suggested.
Thanks!