Solved

Fix country part 2

Posted on 2011-03-07
22
201 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
 
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 45

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 45

Expert Comment

by:aikimark
ID: 35069407
@Wilder1626

Are any cells changing color?
0
 
LVL 45

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 45

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 45

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 45

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now