Help with a very complicated For each Next code

[Ayo]

I have a form with 26 DTPickers and 26 TextBoxes, with each DTPicker
associated to a TextBox by the name i.e. DTPicker1 is next to txtbx2,
DTPicker3 is next to txtbx4, DTPicker5 is next to txtbx6, ..., DTPicker55 is
next to txtbx56.
Now I am trying to write a code that does the following:

1.) For each DTPicker on the form, I want to check its value against every
DTPicker after it, for example if:
DTPicker1.value > DTPicker3.value
then I want to change the Backcolor of txtbx2 to White. In other words, if:
For each DTPicker in UserForm2
For i=1 to 55 step 2
DTPicker(i).value > DTPicker(i+2).value then:
txtbx(i+1).BackColor = &HFFFFFF
txtbx(i+1).ForeColor = &H0&

This is what I am trying to do in a nut shell but I can't figure out exactly
how to code it. Any help will be greatly appreciated.
Thanks
Ayo

 

[Jacob Skaria]

Try the below..

Dim i As Integer
For i = 1 To 55 Step 2
If Me.Controls("DTPicker" & i).Value > _
Me.Controls("DTPicker" & i + 2).Value Then
Me.Controls("txtbx" & i + 1).BackColor = &HFFFFFF
Me.Controls("txtbx" & i + 1).ForeColor = &H0&
End If
Next

If this post helps click Yes

 

[Ayo]

Thanks Jacob. But this only go through and the DTPickers at once. What I am
looking for is more like this:
I want to compare DTPicker1 with DTPicker2, DTPicker3, ...DTPicker55 then
do the same for DTPicker2, i.e.
compare DTPicker2 with DTPicker3, DTPicker4, ...DTPicker55
compare DTPicker3 with DTPicker4, DTPicker5, ...DTPicker55
and so on

 

[Jacob Skaria]

Do you mean the below...But I am sure you can alter this to suit your
requirement...i mean you can refer the controls this way
Me.Controls("DTPicker" & i).

Dim i As Integer, y As Integer
For i = 1 To 55
For y = i + 1 To 55
If Me.Controls("DTPicker" & i).Value > _
Me.Controls("DTPicker" & y).Value Then
Me.Controls("txtbx" & i + 1).BackColor = &HFFFFFF
Me.Controls("txtbx" & i + 1).ForeColor = &H0&
End If
Next
Next

If this post helps click Yes

 

[Ayo]

I am guessing this should work:

Dim i As Integer
Dim j As Integer
For i = 1 To 55 Step 2
For j = i + 2 To 55 Step 2
If Me.Controls("DTPicker" & i).Value > _
Me.Controls("DTPicker" & j).Value Then
if Me.Controls("txtbx" & i + 1).value="Projected"
and _
Me.Controls("txtbx" & j +
1).value="Projected" then
Me.Controls("txtbx" & i +1).BackColor=&HFFFFFF

Me.Controls("txtbx" & i + 1).ForeColor = &H0&
end if
End If
Next j
Next i

 

[Rick Rothstein]

This seems to be a follow up to your previous thread, but you have changed
some things (you are no longer looking for an entry of "actual" and you are
using different ForeColors. Anyway, without setting a UserForm up to test
it, I think your code will work... almost. The problem I see is if you
select a date, apply the code to color the TextBoxes and then change
("correct") a date entry... the colored TextBoxes won't change back to the
default colors. I think this modification to your code should work (again,
remember, this is untested code)...

Dim i As Integer
Dim j As Integer
For i = 1 To 55 Step 2
Me.Controls("txtbx" & i + 1).BackColor = &H80000005
Me.Controls("txtbx" & i + 1).ForeColor = &H80000008
For j = i + 2 To 55 Step 2
If Me.Controls("DTPicker" & i).Value > _
Me.Controls("DTPicker" & j).Value Then
If Me.Controls("txtbx" & i + 1).Value = "Projected" And _
Me.Controls("txtbx" & j + 1).Value = "Projected" Then
Me.Controls("txtbx" & i + 1).BackColor = &HFFFFFF
Me.Controls("txtbx" & i + 1).ForeColor = &H0&
End If
End If
Next j
Next i