Change Colors With 5 Different Conditions

[Minitman]

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
returnHere:
xInt = xlAutomatic: xBdr = xlAutomatic
Next
End If

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

End Sub

 

[Peter T]

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]

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

 

[Peter T]

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]

Hey Peter,

Thanks, I'll try that.

-Minitman

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