Fix country part 2

Hello all,

With that code, i would like to put in color the cell that was change in column 7 and 11.

Actual code:
Dim lngLoop As Long
For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
      End If
  End Select
Next

Open in new window



I started do do something but it is hard:
Dim i As Long

For i = 1 To MSHFlexGrid1.Rows - 1

If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) And (MSHFlexGrid1.TextMatrix(i, 7)) = "CANADA" Then
   MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If

Open in new window


Thanks again for your help

Related Question: http://www.experts-exchange.com/Q_26865931.html
LVL 11
Wilder1626Asked:
Who is Participating?
 
aikimarkCommented:
Here is a more compact version of the code:

Dim lngLoop As Long
Dim boolChanged As Boolean
Dim lngOriginDest As Long

On Error Resume Next

For lngLoop = 1 To MSHFlexGrid1.Rows
  For lngOriginDest = 4 To 8 Step 4
    boolChanged = False
    Select Case Val(MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest))
      Case 5, 8
        If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 2), 1)) Then
          If MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) <> "USA" Then
            boolChanged = True
            MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) = "USA"
          End If
        Else
          If MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) <> "CANADA" Then
            boolChanged = True
            MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) = "CANADA"
          End If
        End If
        If boolChanged Then
          MSHFlexGrid1.Row = lngLoop
          MSHFlexGrid1.Col = lngOriginDest + 3
          MSHFlexGrid1.CellBackColor = &H80FF&
        End If
    End Select
  Next

Next

Open in new window

0
 
Wilder1626Author Commented:
Still not working after this try:

Dim i As Long
Dim lngLoop As Long
For i = 1 To MSHFlexGrid1.Rows - 1
Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8

If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) And (MSHFlexGrid1.TextMatrix(i, 7)) = "CANADA" Then
   MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If

End Select
 Next


For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
      End If
  End Select
Next

Open in new window



I guess i need to put in colors the one that needs to be change before i change it with the rest of the code.


0
 
EDDYKTCommented:
have you put a break point on the line

MSHFlexGrid1.CellBackColor = &H80FF&


see whether it tet execute?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Wilder1626Author Commented:
Hello EDDYKT

What is a break point?
0
 
Wilder1626Author Commented:
Hello aikimark

OK, i will do it for the next time.

Sorry for this.
0
 
EDDYKTCommented:
if you run from VB6 IDE, click on the line and press F9
and then press F5 to run

0
 
Wilder1626Author Commented:
after the break point on :
MSHFlexGrid1.CellBackColor = &H80FF&

Open in new window


It just change the country like it 's suppose  to.

It does not put in color if there was a change.
0
 
aikimarkCommented:
@EDDYKT

Won't you need to reference a particular cell when changing the backcolor?
0
 
EDDYKTCommented:
i thought he already did go through the loop.

However, yes, you should assign the value before set the color
Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
   MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If


      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
   MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If

      End If
  End Select
Next

Open in new window

0
 
EDDYKTCommented:
too fast to click submit
Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case Val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
      End If
  End Select
  Select Case Val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
      End If
  End Select
Next

For i = 1 To MSHFlexGrid1.Rows - 1
Select Case Val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8

If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) And (MSHFlexGrid1.TextMatrix(i, 7)) = "CANADA" Then
   MSHFlexGrid1.row = i
   MSHFlexGrid1.col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If

End Select
 Next

Open in new window

0
 
Wilder1626Author Commented:
I just tried this:
Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
      End If
  End Select
Next

For i = 1 To MSHFlexGrid1.Rows - 1
Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8

If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) And (MSHFlexGrid1.TextMatrix(i, 7)) = "CANADA" Then
   MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If

End Select
 Next

Open in new window



But still the same issue where it change some country but the country cell that was change , is not in color.

But no error.
0
 
EDDYKTCommented:
not 100% sure why. 8->

But should you set back the color first?

For i = 1 To MSHFlexGrid1.Rows - 1
Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7

If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) And (MSHFlexGrid1.TextMatrix(i, 7)) = "CANADA" Then
   MSHFlexGrid1.CellBackColor = &H80FF&
else
   MSHFlexGrid1.CellBackColor = &HFF&  ' this will change to some other color - red

End If

End Select
0
 
aikimarkCommented:
@Wilder1626

Are any cells changing color?
0
 
aikimarkCommented:
In the following example, I try to change the cell color in the same pass as the data change.  I only change color for CANADA changes.

Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 7
        MSHFlexGrid1.CellBackColor = &H80FF&
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 11
        MSHFlexGrid1.CellBackColor = &H80FF&
      End If
  End Select
Next

Open in new window

0
 
Wilder1626Author Commented:
Hello again

I think we are almost there.

With this code:
Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 7
        MSHFlexGrid1.CellBackColor = &H80FF&
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 11
        MSHFlexGrid1.CellBackColor = &H80FF&
      End If
  End Select
Next

Open in new window



Now the color show on CANADA, but for all CANADA. Even those that where already CANADA before.

Is there a way to only put in color those that where changed?
0
 
EDDYKTCommented:
If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 7
        MSHFlexGrid1.CellBackColor = &HFFFFFF&

      Else
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 7
        MSHFlexGrid1.CellBackColor = &H80FF&
0
 
EDDYKTCommented:
if this work, then

some wrong on checking on this loop
For i = 1 To MSHFlexGrid1.Rows - 1
Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8

If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) And (MSHFlexGrid1.TextMatrix(i, 7)) = "CANADA" Then
   MSHFlexGrid1.Row = i
   MSHFlexGrid1.Col = 7
   MSHFlexGrid1.CellBackColor = &H80FF&
End If

End Select
 Next
0
 
aikimarkCommented:
Then try this.
Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        If MSHFlexGrid1.TextMatrix(lngLoop, 11) <> "CANADA" Then
          MSHFlexGrid1.Row = lngLoop
          MSHFlexGrid1.Col = 7
          MSHFlexGrid1.CellBackColor = &H80FF&
        End If
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        If MSHFlexGrid1.TextMatrix(lngLoop, 11) <> "CANADA" Then
          MSHFlexGrid1.Row = lngLoop
          MSHFlexGrid1.Col = 11
          MSHFlexGrid1.CellBackColor = &H80FF&
        End If
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
      End If
  End Select
Next

Open in new window

0
 
Wilder1626Author Commented:
I really appriciate the help from both of you.

Ok now it work for the Canada.

Do i need to double thise code for the USA also?

Dim i As Long
Dim lngLoop As Long

For lngLoop = 1 To MSHFlexGrid1.Rows
On Error Resume Next
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
      Else
        If MSHFlexGrid1.TextMatrix(lngLoop, 11) <> "CANADA" Then
          MSHFlexGrid1.Row = lngLoop
          MSHFlexGrid1.Col = 7
          MSHFlexGrid1.CellBackColor = &H80FF&
        End If
        MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
      End If
  End Select
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
      Else
        If MSHFlexGrid1.TextMatrix(lngLoop, 11) <> "CANADA" Then
          MSHFlexGrid1.Row = lngLoop
          MSHFlexGrid1.Col = 11
          MSHFlexGrid1.CellBackColor = &H80FF&
        End If
        MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
      End If
  End Select
Next

Open in new window

0
 
aikimarkCommented:
Yes.  This is one form of the code.

Dim lngLoop As Long
Dim boolChanged As Boolean

On Error Resume Next

For lngLoop = 1 To MSHFlexGrid1.Rows

  boolChanged = False
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 4))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 6), 1)) Then
        If MSHFlexGrid1.TextMatrix(lngLoop, 7) <> "USA" Then
          boolChanged = True
          MSHFlexGrid1.TextMatrix(lngLoop, 7) = "USA"
        End If
      Else
        If MSHFlexGrid1.TextMatrix(lngLoop, 7) <> "CANADA" Then
          boolChanged = True
          MSHFlexGrid1.TextMatrix(lngLoop, 7) = "CANADA"
        End If
      End If
      If boolChanged Then
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 7
        MSHFlexGrid1.CellBackColor = &H80FF&
      End If
  End Select

  boolChanged = False
  Select Case val(MSHFlexGrid1.TextMatrix(lngLoop, 8))
    Case 5, 8
      If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, 10), 1)) Then
        If MSHFlexGrid1.TextMatrix(lngLoop, 11) <> "USA" Then
          boolChanged = True
          MSHFlexGrid1.TextMatrix(lngLoop, 11) = "USA"
        End If
      Else
        If MSHFlexGrid1.TextMatrix(lngLoop, 11) <> "CANADA" Then
          boolChanged = True
          MSHFlexGrid1.TextMatrix(lngLoop, 11) = "CANADA"
        End If
      End If
      If boolChanged Then
        MSHFlexGrid1.Row = lngLoop
        MSHFlexGrid1.Col = 11
        MSHFlexGrid1.CellBackColor = &H80FF&
      End If
  End Select
Next

Open in new window

0
 
Wilder1626Author Commented:
Oh this is so great.

Thanks again to both of you, i will go with that one:

Dim lngLoop As Long
Dim boolChanged As Boolean
Dim lngOriginDest As Long

On Error Resume Next

For lngLoop = 1 To MSHFlexGrid1.Rows
  For lngOriginDest = 4 To 8 Step 4
    boolChanged = False
    Select Case Val(MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest))
      Case 5, 8
        If IsNumeric(Left$(MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 2), 1)) Then
          If MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) <> "USA" Then
            boolChanged = True
            MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) = "USA"
          End If
        Else
          If MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) <> "CANADA" Then
            boolChanged = True
            MSHFlexGrid1.TextMatrix(lngLoop, lngOriginDest + 3) = "CANADA"
          End If
        End If
        If boolChanged Then
          MSHFlexGrid1.Row = lngLoop
          MSHFlexGrid1.Col = lngOriginDest + 3
          MSHFlexGrid1.CellBackColor = &H80FF&
        End If
    End Select
  Next

Next

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.