I haven't looked at your formulas but it might be easier to break then down
into two or three smaller formulas in separate cells.

Why the "Goto done"

Providing the If-Elseif series is properly constructed that should not be
necessary. The original routine I posted caters for changes in multiple
rows, which can occur with say delete and paste. Goto done will break out of
the for each row loop. If no alternative to goto then try
Goto returnHere
so the next row will get processed.

BTW, don't forget to delete the line spotted by Tom.

Sounds like you are well on your way to completing.

Regards,
Peter T

"Minitman" wrote in message
...
Hey Peter,

In looking at conditions 2 & 3 again, I realized the they are one
If-Then-Else statement, not two:

If you change:
ElseIf .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf .Cells(1, 8) = "Frank" Then
xInt = 39: xBdr = xlAutomatic
.......
done:
Exit Sub

To:
If .Cells(1, 4) = "Nancy" Then
xInt = 6: xBdr = xlAutomatic
GoTo Done
Else
xInt = 39: xBdr = xlAutomatic
GoTo Done
End If
........
done:
xInt = xlAutomatic: xBdr = xlAutomatic
Exit Sub

I haven't tried it yet but it looks like it should work.

As for the CF formulas, they are almost identical. The difference is
the E's and the F's are reversed.
Thus giving total of bank-statement-entries-per-day - total of
deposit-ticket-entries-per-day,
If this figure is a minus figure Then xInt = 38 Else xInt = 37.
The formula as used in the CF had to be flipped so for each condition
when true would have it's own color. I can see where that is not very
practical in VBA

As for the reference to column X, here is the formula in row 2:

=IF(OR(B2="",B2="Not A Service Item"),IF(OR(A2="",A2="Not A Banking
Item"),"999999-9999999999",IF(D2="",A2,A2&"-"&D2)),IF(D2="",B2,B2&"-"&D2))

A2's "Not A Banking Item" is a space filler so that my sorting routine
would work. I added B2's filler for balance, otherwise it is not
needed. I did have to treat both fillers as if they are not there in
this formula so that I could get a reference number that consisted of
a date code and bank account number (or just a date if no bank
account). I sorted with this number leaving me with a date first and
bank account second sort - It did not work if A was empty.

This number is also used to find match entries from the bank
statements and the deposit tickets

Sorry for rambling on, I was thinking as I went and I think I almost
have it. I need to try out what you guys have given and see if I can
make sense of it all. Any additional thoughts would still be
appreciated.

-Minitman


Thank you all for the help and instruction.

-Minitman


On Tue, 26 Jul 2005 00:43:02 +0100, "Peter T" <peter_t@discussions
wrote:

Bit of changing goal posts here but just as well you said, my suggestion
would be some way off doing what you want.

First thing to note is that no event is triggered by values that change
in
otherwise unchanged formula cells. So change -

Set rCheck = Intersect(Target, rMain.Resize(250, 8))
to
Set rCheck = Intersect(Target, rMain)

Your old CF formulas are looking at changes anywhere in columns A-X, not
as
I thought only changes in A and D-H.

One way to incorporate those long formulas would be to enter into helper
cells, perhaps in cols, Y & Z (assuming they are slightly different in
each
row). Much faster than calculating in VBA, no need to extend rCheck as
changing values of these formulas will not trigger an event. However
these
new formulas will need to be looked at in the If-Else series.

I expect you could remove some, but not all, of the relative $ so you can
enter in one cell and copy down, but I'm not sure if these are "per row"
formulas.

$D2""
why not $D2<"" or maybe $D20 if a value

But your two formulas look identical !

I still don't follow your conditions 2 & 3. Seems if cond' 2 is false
then
cond' 3 will always be true and so you will never get to cond's 4 & 5.
I'm
probably missing something, so if it all works for you that's fine.

You will probably find things significantly faster only to change formats
if
they need to be changed, eg in the routine I posted

Dim v as Variant
'code
v = .Interior.ColorIndex = xInt
If Not v Or IsNull(v) Then .Interior.ColorIndex = xInt

and similar for each of all those border changes

Regards,
Peter T

"Minitman" wrote in message
.. .
Hey Peter,

Thank you for this redirection, it looks like this is the approach
that I am looking for.

On the condition 2 & 3 question, it is simply looking for anything in
the D cell. If empty then use one color unless the special case of
Nancy being in the H cell, then use a different color. So there will
be one of two colors if the D cell is empty. If the D cell has any
entry in it then it is a banking item. With banking items, I need to
check to see if there is more on the bank statement or on the deposit
ticket for each bank account per day. The formula's I was using in
the CF to do this are (conditions 2 & 3 were never in the CF):

Condition 4 turns cell pink(38)


=AND($D2"",((ROUND(SUMIF($X$2:$X$250,$X2,$E$2:$E $250),2)-ROUND(SUMIF($X$2:
$
X$250,$X2,$F$2:$F$250),2))0))

Condition 5 turns cell blue (37)


=AND($D2"",((ROUND(SUMIF($X$2:$X$250,$X2,$F$2:$F $250),2)-ROUND(SUMIF($X$2:
$
X$250,$X2,$E$2:$E$250),2))0))

Which brings up the second part of my question, how do I use these
formula in this code? I can see that the $D2"" section is to be
broken off by an IF statement, but what do I do with the rest?

Thanks for the assistance.

-Minitman




On Mon, 25 Jul 2005 18:25:58 +0100, "Peter T" <peter_t@discussions
wrote:

Hi Minitman,

Looking at your conditions in your OP it might be easier to use If
Elseif.

Following is quickly written & totally untested, so don't assume it
meets
your requirements, or doesn't include some other error. Just for ideas

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xInt As Long, xBdr As Long
Dim rw As Long
Dim rMain As Range
Dim rCheck As Range
Dim r As Range

Set Target = Selection
Set rMain = Range("A1:X250")

'only concerned with changes in cols A-H, ie 1-8, right ?
Set rCheck = Intersect(Target, rMain.Resize(250, 8))
If Not rCheck Is Nothing Then
xInt = xlAutomatic: xBdr = xlAutomatic
On Error GoTo errH

For Each r In rCheck.Rows
rw = r.Row
With rMain.Rows(rw)
If Len(.Cells(1, 1)) = 0 And Len(.Cells(1, 2)) = 0
Then
xInt = 19: xBdr = 19
ElseIf .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf .Cells(1, 8) = "Frank" Then
xInt = 39: xBdr = xlAutomatic
ElseIf .Cells(1, 5) .Cells(1, 6) Then
xInt = 37: xBdr = xlAutomatic
ElseIf .Cells(1, 6) .Cells(1, 5) Then
xInt = 38: xBdr = xlAutomatic
End If
End If

.Interior.ColorIndex = xInt

'if style & weight already applied to whole range
'no need to do again
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin

.Borders(xlInsideVertical).ColorIndex = xBdr
.Borders(xlEdgeBottom).ColorIndex = xBdr
End With
returnHe
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

done:
Exit Sub
errH:
If rw Then
Resume returnHere
Else: Resume done
End If

End Sub

You could force it to format the entire range with say

Sub setup()
With Range("A1:H250")
.Copy
.PasteSpecial xlPasteValues
End With
End Sub

Something doesn't seem right with the logic in your conditions 2 & 3.
As
written doesn't look like you will ever get to conditions 4 & 5. Hence
I
changed
H = "anything else" to "Minitman".

Shouldn't need to disable events if only changing formats.

Regards,
Peter T

"Minitman" wrote in message
.. .
Hey Bob,

Now I am confused. In the VBA help, it said something totally
different!

It said that an expression was required when you start the select
case. You code uses .Value they used an expression that is part of
what you are looking for somehow and the cases had the exact bit
that
you wanted to sort by. It makes sense, but not of much use as I
understood it. Your code doesn't do that. Your code looks like it
will do what I need if I Could just see how to get my conditions
into
it.

Your answer appears to answer the question of what I should NAME the
cases. That is not the question!

The question is how do I sort with my conditions so that if any of
them are met then that particular color is the one that is chosen?

Sorry if I was a little unclear, I have been working on this well
past my bed time - like about 9 hours!

Thanks for your assistance.

-One Confused Minitman

.

On Mon, 25 Jul 2005 12:11:07 +0100, "Bob Phillips"
wrote:


"Minitman" wrote in message
.. .
Hey Bob,

Thanks for the sample.

A couple of questions do present them selves.

How are the conditions assigned to the Case's?

I have used numbers, Case 1 etc. You can change to text Case "abc"
etc.

And how do I limit the row to only A thru X?

It already is, by the intersect.