Colour code not updating in correct row where id number is

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,231
Office Version
  1. 2010
Platform
  1. Windows
Hi good morning, please can you help me I have the 2 codes below one for the update button where it updates and finds the ID number in sheet 1 and updates the data into the correct cells, but it should also colour code that line once updated if a specific textbox has data in it. But what it is doing is updating the correct cells with the ne data but the colour code is update on a newline, and not for the row where h data has been updated. Please can you help me please? I am so stuck on this.
Code for update button below.
Code: 
Private Sub CommandButton1_Click()
    Dim Found As Range
    
    If Me.TextBox18.value = "" Then
        MsgBox "No MPAN Found ", , "Missing Entry"
    Else
        Set Found = Sheets("Sheet1").Range("G:G").Find(What:=Me.TextBox18.value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.TextBox18.value, , "No Match Found"
        Else
            Found.Offset(emptyRow, 8).value = Me.ComboBox1.value
            Found.Offset(emptyRow, 9).value = Me.TextBox19.value
            Found.Offset(emptyRow, 10).value = Me.TextBox15.value
            Found.Offset(emptyRow, 11).value = Me.TextBox16.value
            Found.Offset(emptyRow, 12).value = Me.TextBox17.value
            
        End If
    End If
    
End Sub

Colour code below.
Code: 
Private Sub ComboBox1_Change()
  With ThisWorkbook.Sheets("Sheet1")
  emptyRow = .Cells(Rows.Count, "H").End(xlUp).Row + 1
    If ComboBox1.value = "Yes" Then Range(.Cells(emptyRow, 1), .Cells(emptyRow, 19)).Interior.Color = RGB(255, 255, 0)
    If ComboBox1.value = "No" Then Range(.Cells(emptyRow, 1), .Cells(emptyRow, 19)).Interior.Color = RGB(147, 112, 219)
  End With
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Re: Colour code not updating in correct row where id number is, please help

Hi just wondering if you had any ideas please, what this is doing is putting the new correct updated data in the correct cells and into the correct row, but the colour code is going into the wrong row, not in the row where the data has been updated. hope you can help?
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

I cant see how either of them work. How is emptyRow assigned a value in the first one? Then if the variable did have a value you offset that number of cells down from your Found range. That is unlikely to be what you want. The 2nd one just colours some cells at the bottom of the sheet. It may be better to say what you want rather than give code that doesnt do what you want.
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

Hi, just an update with regards to what I am trying to do, I have a userform where TextBox18 is where people will will enter the ID/MPAN Number for a member of staff, this data is then entered on Sheet1. When people have an update they go into userform2 and enter the ID/MPAN number which is in Sheet1 then put it into textbox18 they then go into the other textboxes ie ComboBox1, textbox15, textbox16 and textbox17 with the updates, they then click the update button (Commandbutton1) so the updated data gets added into Sheet1 in the correct rows and in the same line as the ID/MPAN Number. ComboBox1 to TextBox17 are in Rows O to R, i have managed to copy the inforation across to the correct rows, but i dont know how to get the colours to go into the same row can you help on this please, Hope you can help me please?
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

What you have given definately doesnt do what you describe. Give us the code you are using.
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

hi this is the whole code for userform 2
Code: 
Private Sub ComboBox1_Change()
  With ThisWorkbook.Sheets("Sheet1")
  emptyRow = .Cells(Rows.Count, "O").End(xlUp).Row + 1
    If ComboBox1.value = "Yes" Then Range(.Cells(emptyRow, 1), .Cells(emptyRow, 19)).Interior.Color = RGB(255, 255, 0)
    If ComboBox1.value = "No" Then Range(.Cells(emptyRow, 1), .Cells(emptyRow, 19)).Interior.Color = RGB(147, 112, 219)
  End With
End Sub
Private Sub CommandButton1_Click()
    Dim Found As Range
    
    If Me.TextBox18.value = "" Then
        MsgBox "No MPAN Found ", , "Missing Entry"
    Else
        Set Found = Sheets("Sheet1").Range("G:G").Find(What:=Me.TextBox18.value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.TextBox18.value, , "No Match Found"
        Else
            Found.Offset(emptyRow, 8).value = Me.ComboBox1.value
            Found.Offset(emptyRow, 9).value = Me.TextBox19.value
            Found.Offset(emptyRow, 10).value = Me.TextBox15.value
            Found.Offset(emptyRow, 11).value = Me.TextBox16.value
            Found.Offset(emptyRow, 12).value = Me.TextBox17.value
            
        End If
    End If
    
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Unload Me
UserForm2.Show
End Sub
Private Sub Label17_Click()
End Sub




Private Sub TextBox15_Change()
End Sub
Private Sub TextBox16_Change()
End Sub
Private Sub TextBox17_Change()
End Sub
Private Sub TextBox18_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
With Sheets("Lists")
    ComboBox1.RowSource = ""
    ComboBox1.List = .Range("G2:G" & .Range("G" & Rows.Count).End(xlUp).Row).value
End With
End Sub
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

If im right in your intentions then this works:

Code: 
Private Sub CommandButton1_Click()

Dim Found As Range
    
If Me.TextBox18.Value = "" Then
    MsgBox "No MPAN Found ", , "Missing Entry"
Else
    Set Found = Sheets("Sheet1").Range("G:G").Find(What:=Me.TextBox18.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
    If Found Is Nothing Then
        MsgBox "No match for " & Me.TextBox18.Value, , "No Match Found"
    Else
        With ThisWorkbook.Sheets("Sheet1")
            Found.Offset(0, 8).Value = Me.ComboBox1.Value
            Found.Offset(0, 9).Value = Me.TextBox19.Value
            Found.Offset(0, 10).Value = Me.TextBox15.Value
            Found.Offset(0, 11).Value = Me.TextBox16.Value
            Found.Offset(0, 12).Value = Me.TextBox17.Value
            Select Case LCase(Me.ComboBox1.Value)
                Case "yes"
                    .Range(.Cells(Found.Row, 1), .Cells(Found.Row, 19)).Interior.Color = RGB(255, 255, 0)
                Case "no"
                    .Range(.Cells(Found.Row, 1), .Cells(Found.Row, 19)).Interior.Color = RGB(147, 112, 219)
            End Select
        End With
    End If
End If
    
End Sub

You dont need the combobox change code.
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

wow that is great thank you thank you :), how would I implement it to the same sort of code below? this is userform3.
Code: 
Private Sub CommandButton1_Click()
    Dim Found As Range
    
    If Me.TextBox22.value = "" Then
        MsgBox "No MPAN Found ", , "Missing Entry"
    Else
        Set Found = Sheets("Sheet1").Range("G:G").Find(What:=Me.TextBox22.value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.TextBox22.value, , "No Match Found"
        Else
            Found.Offset(emptyRow, 13).value = Me.TextBox19.value
            Found.Offset(emptyRow, 14).value = Me.TextBox20.value
            Found.Offset(emptyRow, 15).value = Me.TextBox21.value

        End If
    End If
    
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Unload Me
UserForm3.Show
End Sub
Private Sub TextBox19_Change()
  With ThisWorkbook.Sheets("Sheet1")
  emptyRow = .Cells(Rows.Count, "T").End(xlUp).Row + 1
    If Len(TextBox19.value) Then Range(.Cells(emptyRow, 1), .Cells(emptyRow, 22)).Interior.Color = RGB(0, 128, 0)
  End With
End Sub
Private Sub TextBox20_Change()
End Sub
Private Sub TextBox21_Change()
End Sub
Private Sub TextBox22_Change()
End Sub
 
Upvote 0
Re: Colour code not updating in correct row where id number is, please help

ive tried altering the code to similar on how you helped out :) but it didn't work its because I think its because of the If Len part, please see below what I tried.
Code: 
Private Sub CommandButton1_Click()
Dim Found As Range
If Me.TextBox22.value = "" Then
    MsgBox "No MPAN Found ", , "Missing Entry"
Else
    Set Found = Sheets("Sheet1").Range("G:G").Find(What:=Me.TextBox22.value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
    If Found Is Nothing Then
        MsgBox "No match for " & Me.TextBox22.value, , "No Match Found"
    Else
        With ThisWorkbook.Sheets("Sheet1")
              Found.Offset(emptyRow, 13).value = Me.TextBox19.value
            Found.Offset(emptyRow, 14).value = Me.TextBox20.value
            Found.Offset(emptyRow, 15).value = Me.TextBox21.value
            Select Case LCase(Me.TextBox19.value)
                Case ""
If Len. Range(.Cells(Found.Row, 1), .Cells(Found.Row, 22)).Interior.Color = RGB(0, 128, 0)
            End Select
        End With
    End If
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
TextBox to count values from ComboBox item
Replies
1
Views
172
Patriot2879
P
P
  • Solved
Code doesn't go on a new line (emptyRow)
Replies
8
Views
254
Fluff
Fluff
P
  • Question
auto refresh listbox2 in userform1
Replies
2
Views
377
Patriot2879
P
J
  • Solved
How to return focus to Userform textbox after process is finished.
Replies
1
Views
679
jvoss
J
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
312
Snakehips
Snakehips

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

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