VBA for MORE Conditional Formating Excel 2003

On a second look try
[VBA]
'Conditional Formats, Looking for Friendly Fronts on Premium Collections Worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Long
Dim iColumn As Long
Dim cell As Range

If Not Intersect(Target, Range("e24:g176")) Is Nothing Then

For Each cell In Target

If cell.Value <> "" Then

Select Case cell.Value
Case "Dom. Republic", "Bolivia", "Paraguay", "Peru", "Costa Rica", "Nicaragua", "Panama"
iColor = 4
iColumn = 5

'Tarrif rated countries must be pre-approved or approved by superintendency
Case "Bangladesh", "Belarus", "Belgium", "Brazil", "Brunei", "China", "Dom. Republic"
iColor = 6
iColumn = 1

'Tarrif rated countries Guatemala must be approved by superintendency
Case "Guatemala"
iColor = 3
iColumn = 1

Case "Spain"
iColor = 14
iColumn = 7

Case "Uganda"
iColor = 6
iColumn = 5

Case "Virgin Islands US"
iColor = 6
iColumn = 3

Case Else
iColor = 20
iColumn = 78

End Select
cell.Offset(0, iColumn).Interior.ColorIndex = iColor
cell.Interior.ColorIndex = iColor
End If
Next cell
End If
End Sub

[/VBA]

I'm no longer having the type mismatch but I do have these problems with the lates VBA Codes.


Problems
1. The colors wont fill in column G unless I click on the cell in the column G individually.
2. I click on the cells inividually and the columns G, I & K will format with the colors needed.
3. If I select 2 or 3 cells in column G the cells will change to the color of the first cell I select
4. If I select the complete column all the cells will change to each different color in the macro code
5. if I change the country to one that is not formatted for a color and place it in a cell that previously had a color coded country that particular cell wont change color unless I select the cell manually
6. If I selected a country that was color coded and later I decide to delete it the cell will remain with its previous formatte color


Any thoughts?...Thanx...


[VBA]'Conditional Formats, Looking for Friendly Fronts on Premium Collections Worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Long
Dim iColumn As Long
Dim cell As Range

If Not Intersect(Target, Range("e24:g176")) Is Nothing Then

For Each cell In Target

If cell.Value <> "" Then

Select Case cell.Value
Case "Dom. Republic", "Bolivia", "Paraguay", "Peru", "Costa Rica", "Nicaragua", "Panama"
iColor = 4
iColumn = 4

'Tarrif rated countries must be pre-approved or approved by superintendency
Case "Bangladesh", "Belarus", "Belgium", "Brazil", "Brunei", "China"
iColor = 6
iColumn = 2

'Tarrif rated countries Guatemala must be approved by superintendency
Case "Guatemala"
iColor = 3
iColumn = 2

Case "Spain"
iColor = 14
iColumn = 2

Case "Uganda"
iColor = 6
iColumn = 2

Case "Virgin Islands US"
iColor = 6
iColumn = 2


Case Else
iColor = 0
iColumn = 4


End Select
Target.Offset(0, iColumn).Interior.ColorIndex = iColor
Target.Interior.ColorIndex = iColor
End If
Next cell
End If
End Sub[/VBA]

Sorry, I edited just after posting so the Target lines were incorrect.
I'm not fully clear on your other issues, but try this version.

Quote:

---

Originally Posted by mdmackillop

Sorry, I edited just after posting so the Target lines were incorrect.
I'm not fully clear on your other issues, but try this version.

---

Wow, kool...it works fantastically.

One other thing, How can the code be changed to format more than one column for each condition met? For example.

This piece of code only lets me format column 2. I also wish to format column 5 with the same color, how is this accomplished?

[VBA]'Tarrif rated countries must be pre-approved or approved by superintendency
Case "Bangladesh", "Belarus", "Belgium", "Brazil", "Brunei", "China"
iColor = 6
iColumn = 2[/VBA]

Thanx once again...

Add another column variable, set that in each case statement, and then add another set of statements after the end select.

Quote:

---

Originally Posted by xld

Add another column variable, set that in each case statement, an d then add another set of statements after the end select.

---

Thanx, I kinda did that but it did not add the color to the second column. Not sure if id did it correctly.


I needd to format color for column 5 for these countries with the same color.

Thanx again...:banghead:

[vba]Case "Bangladesh", "Belarus", "Belgium", "Brazil", "Brunei", "China"
iColor = 6
iColumn = 2[/vba]


Here is the code--
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Columns.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c24:c176")) Is Nothing Then
For Each cell In Target
If cell.Value <> "" Then
MakeChange cell.Offset(, 4)
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next cell
End If

End Sub

'Conditional Formats, Looking for Friendly Fronts on Premium Collections Worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
If Target.Columns.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("e24:g176")) Is Nothing Then
For Each cell In Target
If cell.Value <> "" Then MakeChange cell
Next cell
End If
End Sub

Sub MakeChange(cell As Range)
Dim iColor As Long
Dim iColumn As Long

Select Case cell.Value
Case "Dom. Republic", "Bolivia", "Paraguay", "Peru", "Costa Rica", "Nicaragua", "Panama"
iColor = 4
iColumn = 4

'Tarrif rated countries must be pre-approved or approved by superintendency
Case "Bangladesh", "Belarus", "Belgium", "Brazil", "Brunei", "China"
iColor = 6
iColumn = 2


'Tarrif rated countries Guatemala must be approved by superintendency
Case "Guatemala", "Guatemala"
iColor = 3
iColumn = 5


Case "Spain"
iColor = 14
iColumn = 2

Case "Uganda"
iColor = 6
iColumn = 2

Case "Virgin Islands US"
iColor = 6
iColumn = 2


Case Else
iColor = 0
iColumn = 0


End Select
cell.Resize(, 1).Interior.ColorIndex = xlNone
cell.Offset(0, iColumn).Interior.ColorIndex = iColor
cell.Interior.ColorIndex = iColor




End Sub[/vba]

Like
[VBA]
Case "Guatemala", "Guatemala"
iColor = 3
iColumn = 5
jColumn = 10

'and
cell.Offset(0, iColumn).Interior.ColorIndex = iColor
cell.Offset(0, jColumn).Interior.ColorIndex = iColor
[/VBA]

Quote:

---

Originally Posted by mdmackillop

Like
[VBA]
Case "Guatemala", "Guatemala"
iColor = 3
iColumn = 5
jColumn = 10

'and
cell.Offset(0, iColumn).Interior.ColorIndex = iColor
cell.Offset(0, jColumn).Interior.ColorIndex = iColor
[/VBA]

---

OK hermanos..It is just what I need. Grateful to all the knowledgeable folks that helped me out :bow: "El XID and Mdmackillop" :bow: and this very kool :thumb forum.

I knew just a little bit yesterday, today I know a little bit more and 2morrow, well I;ll wait for 2morrow....i'll be learning even more..

Thanx ....:beerchug: