Cell colour change on userform submit to worksheet

    Hi,
    This should be easy but so far I’m unable to get it correct.
    In the supplied code when the date is entered into the cell in my worksheet I also require the same cell to be Red.


    Code
    1. Private Sub DateTransferButton_Click() Dim sh As Worksheet Dim b As Range Dim wName As String, res As Variant If NameForDateEntryBox.ListIndex = -1 Then MsgBox "Please Select A Customer Before Pressing Transfer Button", vbCritical, "Delivery Parcel Date Transfer Message" Exit Sub End If If TextBox7.Value = "" Or Not IsDate(TextBox7.Value) Then MsgBox "Please Enter A Valid Date", vbCritical, "Delivery Parcel Date Transfer Message" TextBox7 = "" TextBox7.SetFocus Exit Sub End If wName = NameForDateEntryBox.List(NameForDateEntryBox.ListIndex) Set sh = Sheets("POSTAGE") Set b = sh.Columns("B").Find(wName, LookIn:=xlValues, lookat:=xlWhole) If Not b Is Nothing Then If sh.Cells(b.Row, "G").Value <> "" Then MsgBox "DATE HAS BEEN ENTERED ALREADY !" & vbCrLf & "Click OK To Go Check It Out ", vbCritical, "Delivery Parcel Date Transfer Message" TextBox7 = "" Unload PostageTransferSheet Cells(b.Row, "G").Select Else sh.Cells(b.Row, "G").Value = CDate(TextBox7.Value) MsgBox "Delivery Date Updated Sucessfully", vbInformation, "Delivery Parcel Date Transfer Message" Call UserForm_Initialize End If End If NameForDateEntryBox = "" TextBox7 = "" TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy") End Sub

    Hello,


    If we assume your macro is working fine ...


    A tiny modification should do the job :



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Well that job is pretty good work & all works well many thanks for that.


    So well in fact im asking for one more piece of advice going by how good the above turned out.
    I tried to follow the additional code you added but in this case it didnt work for me.


    The following code works as it should but the edit i would like added is when the values from the userform are transfered to my worksheet i would like the cell H to be interior color Red also.
    I see in the code where i type in the text boxes & where they are transfererd to the worksheet but could work out how i get the cell in question to be Red.
    I dont type anything in the textbox in question as currently that cell is blank,it then only gets filled with a date from my code in the post above.


    Do you understand what i mean ?


    So i complete the userform.
    I press my transfer button.
    All my typed values are then entered into the row & in there specified cells.
    BUT
    At the same time of transfer make colour interior on the cell in column H vbRed




    Hello again,


    Sorry .. but your explanation is quite confusing ... at least to me ... :wink:


    Could describe step after step your process ... and where is the hurdle you are facing ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Sorry,


    Currently i complete each field on the userform & press the transfer button.
    This then enters the values from the userform into each cell on my worksheet.


    The above works fine.


    The additional part now would be that when the above is carried out please make cell H on the same row vbRed


    Is that ok ?


    Thanks

    Did the above explain it clearer ?


    Example.
    Enter user form then transfer to worksheet.
    Let’s say row on sheet is 987 so cell H on 987 please change interior Color to Red.


    Then when form form is completed again and transferred to worksheet it will now be on row 988 so also please change interior color to red.


    Same down the list.
    989
    990
    as time goes on.

    When you posted your first macro, there was a variable defined As Range ( Dim b As Range )


    and within this macro the instruction :

    Code
    1. Set b = sh.Columns("B").Find(wName, LookIn:=xlValues, lookat:=xlWhole)

    which allows to determine the Target row ...


    In your second macro, you are only dealing with the Last Row ...


    Quite difficult to guess you actual objective without your workbook ...


    By the way, are you the author of these macros ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    You can test for Column H using RGB ...


    Code
    1. ' Column H is Number 8
    2. .Cells(lastrow + 1, 8).Interior.Color = RGB(255, 0, 0)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Thank but i get an error.


    If i put

    Code
    1. .Cells(lastrow + 1, 8).Interior.Color = RGB(255, 0, 0)


    After the other .Cells code etc i then see an error saying duplicate because of this,


    Code
    1. Dim colorHTML As String, r As String, g As String, b As String


    Did i then put it in the wrong place ?

    The section you are referring to


    .Cells(lastrow + 1, 9).Interior.Color = RGB(r, g, b)


    deals with Column I ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

    Your code ...the one you posted ... deals with Column I ...

    Code
    1. .Cells(lastrow + 1, [COLOR=#0000FF][B]9[/B][/COLOR]).Interior.Color = RGB(r, g, b)


    If you are getting a ' duplicate message ' for Column H ... it means that, within your workbook ... there could be similar instructions ...


    Without your workbook ... no way to guess what is actually happening ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)