Solved

Fix country part 2

Posted on 2011-03-07
22
207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

623 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