Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

see if this will help

http://www.mrexcel.com/archive/Formatting/12054.html

--


Gary


"Minitman" wrote in message
...
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

I have written a number of replies in this NG will code on setting mulitple
colors for 3 conditions

"Minitman" wrote:

Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

One of many examples at http://tinyurl.com/b7kly

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
...
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

Hey Bob,

Thanks for the reply.

I am not sure how to enter the target (A2:X250 on each of 120 sheets).
Or how to reference the Case's. I am only using Columns A, B, D & H.
I am trying to color the entire row within the target range. I am
really at a loss as to how to make this. I have been paging through
the archives and am left with the questions at the start of this
reply.

Any help would be most appreciated.

TIA

-Minitman


On Mon, 25 Jul 2005 09:51:31 +0100, "Bob Phillips"
wrote:

One of many examples at http://tinyurl.com/b7kly

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
.. .
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

This should get you started. It works for every sheet in the workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Sh.Range("A2:X250")) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 1
Case 2: .EntireRow.Interior.ColorIndex = 2
Case 3: .EntireRow.Interior.ColorIndex = 3
Case 4: .EntireRow.Interior.ColorIndex = 4
Case 5: .EntireRow.Interior.ColorIndex = 5
Case 6: .EntireRow.Interior.ColorIndex = 6
End Select

End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


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

Thanks for the reply.

I am not sure how to enter the target (A2:X250 on each of 120 sheets).
Or how to reference the Case's. I am only using Columns A, B, D & H.
I am trying to color the entire row within the target range. I am
really at a loss as to how to make this. I have been paging through
the archives and am left with the questions at the start of this
reply.

Any help would be most appreciated.

TIA

-Minitman


On Mon, 25 Jul 2005 09:51:31 +0100, "Bob Phillips"
wrote:

One of many examples at http://tinyurl.com/b7kly

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Minitman" wrote in message
.. .
Greetings,

I am trying to change the color of a row (A:X) when different
conditions are met.

1st condition:
A and B are both empty.
The row from A to X gets InteriorIndex = 19
Border lines get also get ColorIndex = 19
2nd condition:
D ="" and H = "Nancy"
The row from A to X gets InteriorIndex = 6
Border lines get also get ColorIndex = xlAutomatic
3rd condition:
D = "" (H = anything else)
The row from A to X gets InteriorIndex = 39
Border lines get also get ColorIndex = xlAutomatic
4th condition:
D "" and E F (if D is not empty there will always be either
an E or an F)
The row from A to X gets InteriorIndex = 37
Border lines get also get ColorIndex = xlAutomatic
5th condition:
D "" and FE
The row from A to X gets InteriorIndex = 38
Border lines get also get ColorIndex = xlAutomatic

I tried to use the Conditional Formatter, but it only does 3
conditions. Could someone show me how this can be done?

TIA

-Minitman

Hey Bob,

Thanks for the sample.

A couple of questions do present them selves.

How are the conditions assigned to the Case's?
And how do I limit the row to only A thru X?

-Minitman


On Mon, 25 Jul 2005 10:38:23 +0100, "Bob Phillips"
wrote:

This should get you started. It works for every sheet in the workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Sh.Range("A2:X250")) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 1
Case 2: .EntireRow.Interior.ColorIndex = 2
Case 3: .EntireRow.Interior.ColorIndex = 3
Case 4: .EntireRow.Interior.ColorIndex = 4
Case 5: .EntireRow.Interior.ColorIndex = 5
Case 6: .EntireRow.Interior.ColorIndex = 6
End Select

End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

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

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.

With Target
select Case .Value

is the same as

Select Case Target.Value

which means choose the case based on the value of the cell that triggered
the change event.

As Bob wrote it, it made decisions based on an integer value in the cell
between 1 and 6 inclusive. He advised you to change the conditions to meet
your actual requirements. example, if you want to color the cell if it
contains "abc", then change Case 1 to Case "abc".

--
Regards,
Tom Ogilvy

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

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.

Hey Tom,

Thanks for the explanation, it helped.

That is what I was beginning to suspect. I could not see how that
approach would fill the need that I had stated. Peter's solution
appears to be closer to what I am looking for.

-Minitman



On Mon, 25 Jul 2005 10:15:28 -0400, "Tom Ogilvy"
wrote:

With Target
select Case .Value

is the same as

Select Case Target.Value

which means choose the case based on the value of the cell that triggered
the change event.

As Bob wrote it, it made decisions based on an integer value in the cell
between 1 and 6 inclusive. He advised you to change the conditions to meet
your actual requirements. example, if you want to color the cell if it
contains "abc", then change Case 1 to Case "abc".

Why the
Set Target = Selection

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
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.

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

Thanks for the explanation, it helped.

That is what I was beginning to suspect. I could not see how that
approach would fill the need that I had stated. Peter's solution
appears to be closer to what I am looking for.

It is the same thing, just a different way oif stating it

Select Case Target.Value
Case 1: do A
Case 2: do B

is the same as

If Target.value = 1 Then
do A
ElseIf target.Value =2 Then
Do B

etc.

Why the
Set Target = Selection

Oops,
explanation (excuse!) - I originally wrote the routine in a normal sub prior
to transferring to the event, forgot to delete as intended. Thanks for
pointing out.

typo

Hence I changed H = "anything else" to "Minitman".

Minitman Frank (a 10 yr old who was standing by my side at the time)

Regards,
Peter T



"Peter T" <peter_t@discussions wrote in message
...
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.

"Bob Phillips" wrote in message
...

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

Thanks for the explanation, it helped.

That is what I was beginning to suspect. I could not see how that
approach would fill the need that I had stated. Peter's solution
appears to be closer to what I am looking for.

It is the same thing, just a different way oif stating it

Select Case Target.Value
Case 1: do A
Case 2: do B

is the same as

If Target.value = 1 Then
do A
ElseIf target.Value =2 Then
Do B

etc.


Not sure but the OP may have been confused because his conditions appear to
be different combinations of values from different cells, and some of these
not necessarily in the Target range. If so I think a series If - ElseIf
easier to construct in such a scenario. Or even a combination of Case &
ElseIf

Regards,
Peter T

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.

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.

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.

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.

Hey Peter,

I am only working with one row (one record) at a time. I did catch
Tom's correction (thanks Tom).

I just realized after testing the adjusted code, that I am looking at
to many targets. It keeps cycling every time the UserForm puts down
the contents from each of up to 13 TextBoxes. How do I adjust the
code to look only in H (it is much easier to leave the conditions 1, 4
& 5 in the CF). There is a timing problem - H is pasted down after D.
The D entry sets up the condition for the H entry. If D has no entry
then the code looks to the entry in H and adds the color according to
the input of H ("Callahan, Nancy" present: True - color Index = 19,
False - color index = 6). If D has an entry (a bank account number),
then the code does not even look at the entry in H and the colors are
not wanted - at least this is how it is supposed to work. It is after
D gets an input that the long formulas kick in from the CF (which is
working)

Here is the adjusted code:

'Row 1 is only Headers<
Set rMain = Range("A2:X250")

'I need to check first for changes in D if none then changes in H<
'only concerned with changes in cols A-H, ie 1-8, right ?

Set rCheck = Intersect(Target, rMain)
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 .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf Not .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 39: xBdr = xlAutomatic
End If
End If

.Interior.ColorIndex = xInt
End With
returnHe
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

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

End Sub


On Tue, 26 Jul 2005 14:25:13 +0100, "Peter T" <peter_t@discussions
wrote:

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.

Hi Minitman,

Afraid I don't follow all the latest, where did textboxes suddenly come
from!

How do I adjust the code to look only in H

This is simple enough, either change
Set rMain = Range("A2:X250")
to
Set rMain = Range("H2:H250")

or change
Set rCheck = Intersect(Target, rMain)
to
Set rCheck = Intersect(Target, Range("H2:H250"))

Although the event will run whenever any change on the sheet occurs, code
will only look to do things if the change occurred in one or more cells in
H2:H250. The code also caters by looping each row for changes to multiple
cells in H, if say many have been pasted or cut.

Fine to use a combination of CF & event but presumably some condition(s)
should arise when no CF is applied, instead formatted by the code. Also
unformatted by code when a CF condition applies, if that makes sense.

Looking again at your post, if you are applying values from Textboxes on a
userform, you could disable events while doing that to prevent the event
code from running. If you do that be very careful to ensure .EnableEvents
are always set to True when done, plenty of error handling.

Maybe you could put most of the code in a normal sub and passing to it the
ranges to be concerned with. This sub can then be called either by the event
code or from your userform to clean up everything when required.

Regards,
Peter T

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

I am only working with one row (one record) at a time. I did catch
Tom's correction (thanks Tom).

I just realized after testing the adjusted code, that I am looking at
to many targets. It keeps cycling every time the UserForm puts down
the contents from each of up to 13 TextBoxes. How do I adjust the
code to look only in H (it is much easier to leave the conditions 1, 4
& 5 in the CF). There is a timing problem - H is pasted down after D.
The D entry sets up the condition for the H entry. If D has no entry
then the code looks to the entry in H and adds the color according to
the input of H ("Callahan, Nancy" present: True - color Index = 19,
False - color index = 6). If D has an entry (a bank account number),
then the code does not even look at the entry in H and the colors are
not wanted - at least this is how it is supposed to work. It is after
D gets an input that the long formulas kick in from the CF (which is
working)

Here is the adjusted code:

'Row 1 is only Headers<
Set rMain = Range("A2:X250")

'I need to check first for changes in D if none then changes in H<
'only concerned with changes in cols A-H, ie 1-8, right ?

Set rCheck = Intersect(Target, rMain)
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 .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf Not .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 39: xBdr = xlAutomatic
End If
End If

.Interior.ColorIndex = xInt
End With
returnHe
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

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

End Sub


On Tue, 26 Jul 2005 14:25:13 +0100, "Peter T" <peter_t@discussions
wrote:

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.

Hey Peter,

Thanks for the explanation, I was very fuzzy on that point.

As for the TextBoxes and the UserForm, I do almost all of the data
entry via the UserForm (except for the rare manual correction). The
UserForm has a "Save" button which saves the entered data. The Save
button does not actually "Paste" the data into the cells (it seemed
less confusing to state it this way), it just makes the .Value of the
sheet cell = the .Text value of each TextBox of interest. If your
interested I can post the code for you.

Again, thanks for your assistance.

-Minitman


On Wed, 27 Jul 2005 12:48:57 +0100, "Peter T" <peter_t@discussions
wrote:

Hi Minitman,

Afraid I don't follow all the latest, where did textboxes suddenly come
from!

How do I adjust the code to look only in H

This is simple enough, either change
Set rMain = Range("A2:X250")
to
Set rMain = Range("H2:H250")

or change
Set rCheck = Intersect(Target, rMain)
to
Set rCheck = Intersect(Target, Range("H2:H250"))

Although the event will run whenever any change on the sheet occurs, code
will only look to do things if the change occurred in one or more cells in
H2:H250. The code also caters by looping each row for changes to multiple
cells in H, if say many have been pasted or cut.

Fine to use a combination of CF & event but presumably some condition(s)
should arise when no CF is applied, instead formatted by the code. Also
unformatted by code when a CF condition applies, if that makes sense.

Looking again at your post, if you are applying values from Textboxes on a
userform, you could disable events while doing that to prevent the event
code from running. If you do that be very careful to ensure .EnableEvents
are always set to True when done, plenty of error handling.

Maybe you could put most of the code in a normal sub and passing to it the
ranges to be concerned with. This sub can then be called either by the event
code or from your userform to clean up everything when required.

Regards,
Peter T

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

I am only working with one row (one record) at a time. I did catch
Tom's correction (thanks Tom).

I just realized after testing the adjusted code, that I am looking at
to many targets. It keeps cycling every time the UserForm puts down
the contents from each of up to 13 TextBoxes. How do I adjust the
code to look only in H (it is much easier to leave the conditions 1, 4
& 5 in the CF). There is a timing problem - H is pasted down after D.
The D entry sets up the condition for the H entry. If D has no entry
then the code looks to the entry in H and adds the color according to
the input of H ("Callahan, Nancy" present: True - color Index = 19,
False - color index = 6). If D has an entry (a bank account number),
then the code does not even look at the entry in H and the colors are
not wanted - at least this is how it is supposed to work. It is after
D gets an input that the long formulas kick in from the CF (which is
working)

Here is the adjusted code:

'Row 1 is only Headers<
Set rMain = Range("A2:X250")

'I need to check first for changes in D if none then changes in H<
'only concerned with changes in cols A-H, ie 1-8, right ?

Set rCheck = Intersect(Target, rMain)
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 .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf Not .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 39: xBdr = xlAutomatic
End If
End If

.Interior.ColorIndex = xInt
End With
returnHe
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

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

End Sub


On Tue, 26 Jul 2005 14:25:13 +0100, "Peter T" <peter_t@discussions
wrote:

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.

The event code is not concerned with how the change occurred. Gets triggered
whether you enter, paste or set values from your userform.

Besides disabling events, as I mentioned last time, you could set a global
flag whenever you don't want your event to do anything, say while you are
changing values from your form.

eg,
'in a normal module
Public bDontChange as Boolean

'in the event
If bDontChange then Exit Sub

Again don't forget to set to false when done, but less serious than
forgetting to reset EnableEvents.

Regards,
Peter T

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

Thanks for the explanation, I was very fuzzy on that point.

As for the TextBoxes and the UserForm, I do almost all of the data
entry via the UserForm (except for the rare manual correction). The
UserForm has a "Save" button which saves the entered data. The Save
button does not actually "Paste" the data into the cells (it seemed
less confusing to state it this way), it just makes the .Value of the
sheet cell = the .Text value of each TextBox of interest. If your
interested I can post the code for you.

Again, thanks for your assistance.

-Minitman


On Wed, 27 Jul 2005 12:48:57 +0100, "Peter T" <peter_t@discussions
wrote:

Hi Minitman,

Afraid I don't follow all the latest, where did textboxes suddenly come
from!

How do I adjust the code to look only in H

This is simple enough, either change
Set rMain = Range("A2:X250")
to
Set rMain = Range("H2:H250")

or change
Set rCheck = Intersect(Target, rMain)
to
Set rCheck = Intersect(Target, Range("H2:H250"))

Although the event will run whenever any change on the sheet occurs, code
will only look to do things if the change occurred in one or more cells
in
H2:H250. The code also caters by looping each row for changes to
multiple
cells in H, if say many have been pasted or cut.

Fine to use a combination of CF & event but presumably some condition(s)
should arise when no CF is applied, instead formatted by the code. Also
unformatted by code when a CF condition applies, if that makes sense.

Looking again at your post, if you are applying values from Textboxes on
a
userform, you could disable events while doing that to prevent the event
code from running. If you do that be very careful to ensure .EnableEvents
are always set to True when done, plenty of error handling.

Maybe you could put most of the code in a normal sub and passing to it
the
ranges to be concerned with. This sub can then be called either by the
event
code or from your userform to clean up everything when required.

Regards,
Peter T

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

I am only working with one row (one record) at a time. I did catch
Tom's correction (thanks Tom).

I just realized after testing the adjusted code, that I am looking at
to many targets. It keeps cycling every time the UserForm puts down
the contents from each of up to 13 TextBoxes. How do I adjust the
code to look only in H (it is much easier to leave the conditions 1, 4
& 5 in the CF). There is a timing problem - H is pasted down after D.
The D entry sets up the condition for the H entry. If D has no entry
then the code looks to the entry in H and adds the color according to
the input of H ("Callahan, Nancy" present: True - color Index = 19,
False - color index = 6). If D has an entry (a bank account number),
then the code does not even look at the entry in H and the colors are
not wanted - at least this is how it is supposed to work. It is after
D gets an input that the long formulas kick in from the CF (which is
working)

Here is the adjusted code:

'Row 1 is only Headers<
Set rMain = Range("A2:X250")

'I need to check first for changes in D if none then changes in H<
'only concerned with changes in cols A-H, ie 1-8, right ?

Set rCheck = Intersect(Target, rMain)
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 .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf Not .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 39: xBdr = xlAutomatic
End If
End If

.Interior.ColorIndex = xInt
End With
returnHe
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

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

End Sub


On Tue, 26 Jul 2005 14:25:13 +0100, "Peter T" <peter_t@discussions
wrote:

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.

Hey Peter,

Thanks, I'll try that.

-Minitman


On Wed, 27 Jul 2005 20:59:37 +0100, "Peter T" <peter_t@discussions
wrote:

The event code is not concerned with how the change occurred. Gets triggered
whether you enter, paste or set values from your userform.

Besides disabling events, as I mentioned last time, you could set a global
flag whenever you don't want your event to do anything, say while you are
changing values from your form.

eg,
'in a normal module
Public bDontChange as Boolean

'in the event
If bDontChange then Exit Sub

Again don't forget to set to false when done, but less serious than
forgetting to reset EnableEvents.

Regards,
Peter T

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

Thanks for the explanation, I was very fuzzy on that point.

As for the TextBoxes and the UserForm, I do almost all of the data
entry via the UserForm (except for the rare manual correction). The
UserForm has a "Save" button which saves the entered data. The Save
button does not actually "Paste" the data into the cells (it seemed
less confusing to state it this way), it just makes the .Value of the
sheet cell = the .Text value of each TextBox of interest. If your
interested I can post the code for you.

Again, thanks for your assistance.

-Minitman


On Wed, 27 Jul 2005 12:48:57 +0100, "Peter T" <peter_t@discussions
wrote:

Hi Minitman,

Afraid I don't follow all the latest, where did textboxes suddenly come
from!

How do I adjust the code to look only in H

This is simple enough, either change
Set rMain = Range("A2:X250")
to
Set rMain = Range("H2:H250")

or change
Set rCheck = Intersect(Target, rMain)
to
Set rCheck = Intersect(Target, Range("H2:H250"))

Although the event will run whenever any change on the sheet occurs, code
will only look to do things if the change occurred in one or more cells
in
H2:H250. The code also caters by looping each row for changes to
multiple
cells in H, if say many have been pasted or cut.

Fine to use a combination of CF & event but presumably some condition(s)
should arise when no CF is applied, instead formatted by the code. Also
unformatted by code when a CF condition applies, if that makes sense.

Looking again at your post, if you are applying values from Textboxes on
a
userform, you could disable events while doing that to prevent the event
code from running. If you do that be very careful to ensure .EnableEvents
are always set to True when done, plenty of error handling.

Maybe you could put most of the code in a normal sub and passing to it
the
ranges to be concerned with. This sub can then be called either by the
event
code or from your userform to clean up everything when required.

Regards,
Peter T

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

I am only working with one row (one record) at a time. I did catch
Tom's correction (thanks Tom).

I just realized after testing the adjusted code, that I am looking at
to many targets. It keeps cycling every time the UserForm puts down
the contents from each of up to 13 TextBoxes. How do I adjust the
code to look only in H (it is much easier to leave the conditions 1, 4
& 5 in the CF). There is a timing problem - H is pasted down after D.
The D entry sets up the condition for the H entry. If D has no entry
then the code looks to the entry in H and adds the color according to
the input of H ("Callahan, Nancy" present: True - color Index = 19,
False - color index = 6). If D has an entry (a bank account number),
then the code does not even look at the entry in H and the colors are
not wanted - at least this is how it is supposed to work. It is after
D gets an input that the long formulas kick in from the CF (which is
working)

Here is the adjusted code:

'Row 1 is only Headers<
Set rMain = Range("A2:X250")

'I need to check first for changes in D if none then changes in H<
'only concerned with changes in cols A-H, ie 1-8, right ?

Set rCheck = Intersect(Target, rMain)
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 .Cells(1, 4) = "" Then
If .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 6: xBdr = xlAutomatic
ElseIf Not .Cells(1, 8) = "Callahan, Nancy" Then
xInt = 39: xBdr = xlAutomatic
End If
End If

.Interior.ColorIndex = xInt
End With
returnHe
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

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

End Sub


On Tue, 26 Jul 2005 14:25:13 +0100, "Peter T" <peter_t@discussions
wrote:

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.