Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Fix country part 2

Posted on 2011-03-07
22
Medium Priority
?
212 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Wilder1626
  • 8
  • 7
  • 6
22 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 35063428
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35068525
have you put a break point on the line

MSHFlexGrid1.CellBackColor = &H80FF&


see whether it tet execute?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35068756
Hello EDDYKT

What is a break point?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Author Comment

by:Wilder1626
ID: 35068763
Hello aikimark

OK, i will do it for the next time.

Sorry for this.
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35068781
if you run from VB6 IDE, click on the line and press F9
and then press F5 to run

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35068873
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35068928
@EDDYKT

Won't you need to reference a particular cell when changing the backcolor?
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35069000
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35069021
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 35069147
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35069381
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35069407
@Wilder1626

Are any cells changing color?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35069509
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 35069838
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35069897
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
 
LVL 26

Expert Comment

by:EDDYKT
ID: 35069904
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35070077
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 35070172
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
 
LVL 46

Expert Comment

by:aikimark
ID: 35070729
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
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 35070800
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 35071008
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question