Minor edit to working code

I

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,226
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I have this working code shown below.
With Regards TextBox 10 it either enters £0.00 or the value from column S

What i am trying to do is the following,

£0.00 should be interior color vbYellow
BUT
If value from column S then interior vbRed

Ive tried a few variations to apply this but just keep breaking the code

Rich (BB code): 
Private Sub ComboBox1_Change()

Dim i As Long, LastRow As Long
LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
 
    With Sheets("GRASS")

    If .Cells(i, "A").Value = (Me.ComboBox1) Or .Cells(i, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(i, "C").Value 'ADDRESS
                Me.TextBox2 = .Cells(i, "E").Value 'TELEPHONE NUMBER
                Me.TextBox3 = .Cells(i, "I").Value 'AREA
                Me.TextBox4 = .Cells(i, "G").Value 'POST CODE
                Me.TextBox5 = .Cells(i, "Y").Value 'MILES
                Me.TextBox6 = Format(.Cells(i, "K").Value, "£#,##0.00") 'COST
                Me.TextBox7 = .Cells(i, "M").Value 'NEXT DUE
                Me.TextBox8 = .Cells(i, "Q").Value 'PAYMENT TYPE
                Me.TextBox9 = .Cells(i, "U").Value 'DURATION

    If Not IsEmpty(.Cells(i, "S").Value) Then Me.TextBox10.Value = Format(.Cells(i, "S").Value, "£#,##0.00"): GoTo nxt_line

                Me.TextBox10.Value = "£0.00" 'OWES
               
nxt_line:      Me.TextBox11 = .Cells(i, "W").Value 'NOTES
               Me.TextBox12 = .Cells(i, "O").Value 'LAST CUT
   
    Exit For
    End If
    End With
    Next
       
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
This is where im at but TextBox is always Red

VBA Code: 
Private Sub ComboBox1_Change()

Dim i As Long, LastRow As Long
LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
 
    With Sheets("GRASS")

    If .Cells(i, "A").Value = (Me.ComboBox1) Or .Cells(i, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(i, "C").Value 'ADDRESS
                Me.TextBox2 = .Cells(i, "E").Value 'TELEPHONE NUMBER
                Me.TextBox3 = .Cells(i, "I").Value 'AREA
                Me.TextBox4 = .Cells(i, "G").Value 'POST CODE
                Me.TextBox5 = .Cells(i, "Y").Value 'MILES
                Me.TextBox6 = Format(.Cells(i, "K").Value, "£#,##0.00") 'COST
                Me.TextBox7 = .Cells(i, "M").Value 'NEXT DUE
                Me.TextBox8 = .Cells(i, "Q").Value 'PAYMENT TYPE
                Me.TextBox9 = .Cells(i, "U").Value 'DURATION

    If Not IsEmpty(.Cells(i, "S").Value) Then Me.TextBox10.Value = Format(.Cells(i, "S").Value, "£#,##0.00"): GoTo nxt_line

                Me.TextBox10.Value = "£0.00"
                
nxt_line:      Me.TextBox11 = .Cells(i, "W").Value
               Me.TextBox12 = .Cells(i, "Y").Value
    
    Exit For
            End If
            
          End With
          If TextBox10 = "£0.00" Then
          TextBox10.BackColor = RGB(255, 0, 0)
          Else
          TextBox10.BackColor = RGB(255, 255, 0)
          End If
    Next
        
End Sub
 
Upvote 0
Another try which also just gives me vbRed ??

VBA Code: 
Private Sub ComboBox1_Change()

Dim i As Long, LastRow As Long
LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
 
    With Sheets("GRASS")

    If .Cells(i, "A").Value = (Me.ComboBox1) Or .Cells(i, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(i, "C").Value 'ADDRESS
                Me.TextBox2 = .Cells(i, "E").Value 'TELEPHONE NUMBER
                Me.TextBox3 = .Cells(i, "I").Value 'AREA
                Me.TextBox4 = .Cells(i, "G").Value 'POST CODE
                Me.TextBox5 = .Cells(i, "Y").Value 'MILES
                Me.TextBox6 = Format(.Cells(i, "K").Value, "£#,##0.00") 'COST
                Me.TextBox7 = .Cells(i, "M").Value 'NEXT DUE
                Me.TextBox8 = .Cells(i, "Q").Value 'PAYMENT TYPE
                Me.TextBox9 = .Cells(i, "U").Value 'DURATION

    If Not IsEmpty(.Cells(i, "S").Value) Then Me.TextBox10.Value = Format(.Cells(i, "S").Value, "£#,##0.00"): GoTo nxt_line

                Me.TextBox10.Value = "£0.00"
                
nxt_line:      Me.TextBox11 = .Cells(i, "W").Value
               Me.TextBox12 = .Cells(i, "Y").Value
    
    Exit For
    End If
            
          End With
          With TextBox10
          If IsNumeric(.Value) Then
            If .Value > 0 Then
                .BackColor = vbRed
            ElseIf .Value < 0 Then
                .BackColor = vbYellow
          End If
          End If
          End With
    Next
        
End Sub
 
Upvote 0
Why are you exiting the loop prematurely?
 
Upvote 0
Probably because I’m not sure what I’m doing.
should be easy enough but after an hour I’ve stopped as not getting anywhere with it.
 
Upvote 0
Try this.
VBA Code: 
Private Sub ComboBox1_Change()
Dim I As Long, LastRow As Long

    LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
    
    For I = 2 To LastRow

        With Sheets("GRASS")

            If .Cells(I, "A").Value = (Me.ComboBox1) Or .Cells(I, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(I, "C").Value    'ADDRESS
                Me.TextBox2 = .Cells(I, "E").Value    'TELEPHONE NUMBER
                Me.TextBox3 = .Cells(I, "I").Value    'AREA
                Me.TextBox4 = .Cells(I, "G").Value    'POST CODE
                Me.TextBox5 = .Cells(I, "Y").Value    'MILES
                Me.TextBox6 = Format(.Cells(I, "K").Value, "£#,##0.00")    'COST
                Me.TextBox7 = .Cells(I, "M").Value    'NEXT DUE
                Me.TextBox8 = .Cells(I, "Q").Value    'PAYMENT TYPE
                Me.TextBox9 = .Cells(I, "U").Value    'DURATION

                With Me.TextBox10
                    If Not IsEmpty(.Cells(I, "S").Value) Then
                        Me.TextBox10.Value = Format(.Cells(I, "S").Value, "£#,##0.00")
                        .BackColor = vbRed
                    Else
                        Me.TextBox10.Value = "£0.00"
                        .BackColor = vbYellow
                    End If
                End With
                
                Me.TextBox11 = .Cells(I, "W").Value
                Me.TextBox12 = .Cells(I, "Y").Value

                Exit For
            End If

        End With
        
    Next I

End Sub
 
Upvote 0
Morning,
With that code i get a message,
Method or data member not found.

This line is shown in yellow


If Not IsEmpty(.Cells(I, "S").Value) Then
 
Upvote 0
Oops, shouldn't have added that other With.

Try this.
Code: 
Private Sub ComboBox1_Change()
Dim I As Long, LastRow As Long

    LastRow = Sheets("GRASS").Range("A" & Rows.Count).End(xlUp).Row
   
    For I = 2 To LastRow

        With Sheets("GRASS")

            If .Cells(I, "A").Value = (Me.ComboBox1) Or .Cells(I, "A").Value = Val(Me.ComboBox1) Then
                Me.TextBox1 = .Cells(I, "C").Value    'ADDRESS
                Me.TextBox2 = .Cells(I, "E").Value    'TELEPHONE NUMBER
                Me.TextBox3 = .Cells(I, "I").Value    'AREA
                Me.TextBox4 = .Cells(I, "G").Value    'POST CODE
                Me.TextBox5 = .Cells(I, "Y").Value    'MILES
                Me.TextBox6 = Format(.Cells(I, "K").Value, "£#,##0.00")    'COST
                Me.TextBox7 = .Cells(I, "M").Value    'NEXT DUE
                Me.TextBox8 = .Cells(I, "Q").Value    'PAYMENT TYPE
                Me.TextBox9 = .Cells(I, "U").Value    'DURATION

                
                    If Not IsEmpty(.Cells(I, "S").Value) Then
                        Me.TextBox10.Value = Format(.Cells(I, "S").Value, "£#,##0.00")
                        Me.TextBox10.BackColor = vbRed
                    Else
                        Me.TextBox10.Value = "£0.00"
                        Me.TextBox10 .BackColor = vbYellow
                    End If
                
               
                Me.TextBox11 = .Cells(I, "W").Value
                Me.TextBox12 = .Cells(I, "Y").Value

                Exit For
            End If

        End With
       
    Next I

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Move existing row of value to another selected row
Replies
1
Views
131
ipbr21054
I
S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
133
Domenic
D
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
217
amoverton2
A
S
  • Solved
User Form Textbox looping
2
Replies
12
Views
584
sjtjr73
S
I
  • Solved
Apply continuous border code to specific cells / columns only
Replies
2
Views
270
ipbr21054
I

Forum statistics

Threads
1,214,943
Messages
6,122,380
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top