Solved

# Fix country part 2

Posted on 2011-03-07
201 Views
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
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
End If
End Select
Next
``````

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

Related Question: http://www.experts-exchange.com/Q_26865931.html
0
Question by:Wilder1626
• 8
• 7
• 6

LVL 11

Author Comment

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
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
End If
End Select
Next
``````

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

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

MSHFlexGrid1.CellBackColor = &H80FF&

see whether it tet execute?
0

LVL 11

Author Comment

ID: 35068756
Hello EDDYKT

What is a break point?
0

LVL 11

Author Comment

ID: 35068763
Hello aikimark

OK, i will do it for the next time.

Sorry for this.
0

LVL 26

Expert Comment

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

ID: 35068873
after the break point on :
``````MSHFlexGrid1.CellBackColor = &H80FF&
``````

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

ID: 35068928
@EDDYKT

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

LVL 26

Expert Comment

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
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
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
``````
0

LVL 26

Expert Comment

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
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
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
``````
0

LVL 11

Author Comment

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

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

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

ID: 35069407
@Wilder1626

Are any cells changing color?
0

LVL 45

Expert Comment

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.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.Row = lngLoop
MSHFlexGrid1.Col = 11
MSHFlexGrid1.CellBackColor = &H80FF&
End If
End Select
Next
``````
0

LVL 11

Author Comment

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.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.Row = lngLoop
MSHFlexGrid1.Col = 11
MSHFlexGrid1.CellBackColor = &H80FF&
End If
End Select
Next
``````

Is there a way to only put in color those that where changed?
0

LVL 26

Expert Comment

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.Row = lngLoop
MSHFlexGrid1.Col = 7
MSHFlexGrid1.CellBackColor = &H80FF&
0

LVL 26

Expert Comment

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

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
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
End If
End Select
Next
``````
0

LVL 11

Author Comment

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
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
End If
End Select
Next
``````
0

LVL 45

Expert Comment

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
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
End If
End If
If boolChanged Then
MSHFlexGrid1.Row = lngLoop
MSHFlexGrid1.Col = 11
MSHFlexGrid1.CellBackColor = &H80FF&
End If
End Select
Next
``````
0

LVL 45

Accepted Solution

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
``````
0

LVL 11

Author Closing Comment

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
``````
0

## Featured Post

### Suggested Solutions

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â€¦