Change Colors With 5 Different Conditions

[Minitman]

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 F>E
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

 

[Gary Keramidas]

see if this will help

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

 

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

..

 

[Tom Ogilvy]

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

 

[Peter T]

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
returnHere:
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]

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

 

[Tom Ogilvy]

Why the
Set Target = Selection

 

[Bob Phillips]

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.

 

[Peter T]

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

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

Regards,
Peter T

 

[Peter T]

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

 

[Minitman]

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

 

[Peter T]

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 $D2>0 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]

Hey Peter,

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

If you change:
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

 

[Peter T]

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