Change forecolor in Listview depending on value excel vba
up vote
0
down vote
favorite
Hope you can help me... I have a displayed data on my listview. The column Headers are:
ROW ID CUSTOMER PICKUP DELIVERY LOAD PLACE BAGS AMOUNT STATUS -total of 10 columns
I want the forecolor in my column STATUS depends on the value. the value is either PAID or UNPAID, if PAID the color should be green and if UNPAID, the color should be red.
I have this code, but it not working for me, somebody will help me? Thank you in advance.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To listView1.ListItems.Count
Set Item = listView1.ListItems.Item(counter)
If Item.SubItems(10) = "Paid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = vbGreen
End If
If Item.SubItems(10) = "Unpaid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = VBRed
Next counter
End Sub
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
asked Nov 10 at 2:32
dio123
65
add a comment |
up vote
0
down vote
favorite
Hope you can help me... I have a displayed data on my listview. The column Headers are:
ROW ID CUSTOMER PICKUP DELIVERY LOAD PLACE BAGS AMOUNT STATUS -total of 10 columns
I want the forecolor in my column STATUS depends on the value. the value is either PAID or UNPAID, if PAID the color should be green and if UNPAID, the color should be red.
I have this code, but it not working for me, somebody will help me? Thank you in advance.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To listView1.ListItems.Count
Set Item = listView1.ListItems.Item(counter)
If Item.SubItems(10) = "Paid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = vbGreen
End If
If Item.SubItems(10) = "Unpaid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = VBRed
Next counter
End Sub
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
asked Nov 10 at 2:32
dio123
65
not 10 , to be 9. Item.SubItems(10) ~~~>Item.SubItems(9)
– Dy.Lee
Nov 10 at 4:33
@Dy.Lee I tried it already still not working
– dio123
Nov 10 at 4:44
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Hope you can help me... I have a displayed data on my listview. The column Headers are:
ROW ID CUSTOMER PICKUP DELIVERY LOAD PLACE BAGS AMOUNT STATUS -total of 10 columns
I want the forecolor in my column STATUS depends on the value. the value is either PAID or UNPAID, if PAID the color should be green and if UNPAID, the color should be red.
I have this code, but it not working for me, somebody will help me? Thank you in advance.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To listView1.ListItems.Count
Set Item = listView1.ListItems.Item(counter)
If Item.SubItems(10) = "Paid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = vbGreen
End If
If Item.SubItems(10) = "Unpaid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = VBRed
Next counter
End Sub
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
asked Nov 10 at 2:32
dio123
65
Hope you can help me... I have a displayed data on my listview. The column Headers are:
ROW ID CUSTOMER PICKUP DELIVERY LOAD PLACE BAGS AMOUNT STATUS -total of 10 columns
I want the forecolor in my column STATUS depends on the value. the value is either PAID or UNPAID, if PAID the color should be green and if UNPAID, the color should be red.
I have this code, but it not working for me, somebody will help me? Thank you in advance.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To listView1.ListItems.Count
Set Item = listView1.ListItems.Item(counter)
If Item.SubItems(10) = "Paid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = vbGreen
End If
If Item.SubItems(10) = "Unpaid" Then
listView1.ListItems.Item(counter).ListSubItems(10).ForeColor = VBRed
Next counter
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
asked Nov 10 at 2:32
dio123
65
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
asked Nov 10 at 2:32
dio123
65
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
edited Nov 12 at 7:19
Pᴇʜ
19.6k42650
19.6k42650
asked Nov 10 at 2:32
dio123
65
asked Nov 10 at 2:32
dio123
65
asked Nov 10 at 2:32
dio123
65
65
not 10 , to be 9. Item.SubItems(10) ~~~>Item.SubItems(9)
– Dy.Lee
Nov 10 at 4:33
@Dy.Lee I tried it already still not working
– dio123
Nov 10 at 4:44
add a comment |
not 10 , to be 9. Item.SubItems(10) ~~~>Item.SubItems(9)
– Dy.Lee
Nov 10 at 4:33
@Dy.Lee I tried it already still not working
– dio123
Nov 10 at 4:44
not 10 , to be 9. Item.SubItems(10) ~~~>Item.SubItems(9)
– Dy.Lee
Nov 10 at 4:33
not 10 , to be 9. Item.SubItems(10) ~~~>Item.SubItems(9)
– Dy.Lee
Nov 10 at 4:33
@Dy.Lee I tried it already still not working
– dio123
Nov 10 at 4:44
@Dy.Lee I tried it already still not working
– dio123
Nov 10 at 4:44
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
listsubitime must be 9, since index numbers start at 0.
In my test, it work well.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
Dim li As ListItem
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
Dim vDB
vDB = Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
Set li = ListView1.ListItems.Add
For j = 1 To UBound(vDB, 2)
With li
.Text = i
.ListSubItems.Add , , vDB(i, j)
End With
Next j
Next i
'**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To ListView1.ListItems.Count
Set Item = ListView1.ListItems.Item(counter)
If Item.SubItems(9) = "Paid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbGreen
End If
If Item.SubItems(9) = "Unpaid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbRed
End If
Next counter
End Sub
answered Nov 10 at 4:56
Dy.Lee
3,032159
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
listsubitime must be 9, since index numbers start at 0.
In my test, it work well.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
Dim li As ListItem
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
Dim vDB
vDB = Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
Set li = ListView1.ListItems.Add
For j = 1 To UBound(vDB, 2)
With li
.Text = i
.ListSubItems.Add , , vDB(i, j)
End With
Next j
Next i
'**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To ListView1.ListItems.Count
Set Item = ListView1.ListItems.Item(counter)
If Item.SubItems(9) = "Paid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbGreen
End If
If Item.SubItems(9) = "Unpaid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbRed
End If
Next counter
End Sub
answered Nov 10 at 4:56
Dy.Lee
3,032159
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
add a comment |
up vote
0
down vote
accepted
listsubitime must be 9, since index numbers start at 0.
In my test, it work well.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
Dim li As ListItem
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
Dim vDB
vDB = Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
Set li = ListView1.ListItems.Add
For j = 1 To UBound(vDB, 2)
With li
.Text = i
.ListSubItems.Add , , vDB(i, j)
End With
Next j
Next i
'**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To ListView1.ListItems.Count
Set Item = ListView1.ListItems.Item(counter)
If Item.SubItems(9) = "Paid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbGreen
End If
If Item.SubItems(9) = "Unpaid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbRed
End If
Next counter
End Sub
answered Nov 10 at 4:56
Dy.Lee
3,032159
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
listsubitime must be 9, since index numbers start at 0.
In my test, it work well.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
Dim li As ListItem
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
Dim vDB
vDB = Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
Set li = ListView1.ListItems.Add
For j = 1 To UBound(vDB, 2)
With li
.Text = i
.ListSubItems.Add , , vDB(i, j)
End With
Next j
Next i
'**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To ListView1.ListItems.Count
Set Item = ListView1.ListItems.Item(counter)
If Item.SubItems(9) = "Paid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbGreen
End If
If Item.SubItems(9) = "Unpaid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbRed
End If
Next counter
End Sub
answered Nov 10 at 4:56
Dy.Lee
3,032159
listsubitime must be 9, since index numbers start at 0.
In my test, it work well.
Private Sub UserForm_Activate()
Dim C As Long
Dim i As Long
Dim R As Long
Dim li As ListItem
ListView1.View = lvwReport
ListView1.HideSelection = False
ListView1.FullRowSelect = True
ListView1.HotTracking = True
ListView1.HoverSelection = False
ListView1.ColumnHeaders.Add Text:="Row", Width:=40
For C = 1 To 12
ListView1.ColumnHeaders.Add Text:=Cells(1, C).Text
ComboBox1.AddItem Cells(1, C).Text
Next C
Dim vDB
vDB = Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
Set li = ListView1.ListItems.Add
For j = 1 To UBound(vDB, 2)
With li
.Text = i
.ListSubItems.Add , , vDB(i, j)
End With
Next j
Next i
'**' |In this part of my code is not working|**
Dim Item As ListItem
Dim counter As Long
For counter = 1 To ListView1.ListItems.Count
Set Item = ListView1.ListItems.Item(counter)
If Item.SubItems(9) = "Paid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbGreen
End If
If Item.SubItems(9) = "Unpaid" Then
ListView1.ListItems.Item(counter).ListSubItems(9).ForeColor = vbRed
End If
Next counter
End Sub
answered Nov 10 at 4:56
Dy.Lee
3,032159
answered Nov 10 at 4:56
Dy.Lee
3,032159
answered Nov 10 at 4:56
Dy.Lee
3,032159
answered Nov 10 at 4:56
Dy.Lee
3,032159
3,032159
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
add a comment |
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
Thank you for your help!. So much appreciated!
– dio123
Nov 10 at 5:01
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
@dio123, I am glad that it works well.
– Dy.Lee
Nov 10 at 5:02
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53235541%2fchange-forecolor-in-listview-depending-on-value-excel-vba%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
not 10 , to be 9. Item.SubItems(10) ~~~>Item.SubItems(9)
– Dy.Lee
Nov 10 at 4:33
@Dy.Lee I tried it already still not working
– dio123
Nov 10 at 4:44