Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: If cell has no value than change interior colour
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell has no value than change interior colour

    MAIT

    There is also this on the page so thought i might advise you of it

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Application.ScreenUpdating = False
        Dim lastrow As Long
        lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If Intersect(Target, Range("A8:I" & lastrow)) Is Nothing Then Exit Sub
        If Target.Column <> 4 Then
            Range("A8:C" & lastrow).Interior.ColorIndex = 6
            Range("E8:I" & lastrow).Interior.ColorIndex = 6
            Target.Interior.ColorIndex = 8
        End If
        Application.ScreenUpdating = True
    End Sub
    Private Sub Worksheet_Activate()
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #12
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: If cell has no value than change interior colour

    It now appears as if your wanting 3 different things to happen in your sheet.

    You provided your original code which you said worked.
    Then you asked for another code you wanted to change the cell color then you provided additional code which runs when you select different cells.

    It would be best if you told us in words what your wanting and allow us to provide the code you need.

    It gets a little more complicated when you want three pieces of code in the same sheet.
    But it can be done.

    Please explain in detail in words what your wanting. We would then need to write code that would do all three things you want.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #13
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell has no value than change interior colour

    No,
    I was showing in the last post that a color change code is in use and mabe conflict with what you are doing.

    This is what i would like & info for you.

    My range is A9:I9
    A7:I7 are headers
    Row A8 is hidden
    Column A & G are dates
    The other columns are a mixture of letters & numbers.
    All cells must show capital letters so Ucase is needed.
    All cells start as a yellow interior color.
    Any cell on the worksheet with the given range once selected turns blue,once i leave the cell it then returns to yellow & the next selected cell then turns blue.
    Column A is the customers name & Column G is the date the parcel was delivered.
    So until the parcel is delivered the cell in row G for that customer is empty.
    These cells need to be interior color Red.
    Once the parcel is delivered & a date is the entered into that cell the Red would not be applied as it now has a value so it is then changed to yellow.

    I have a userform which i select the customers name & the day i select it then that days date is what is entered into the cell.

    Also on the userform is a question whether or not a security mark was added to the part sold.
    If i select NO then nothing happens to the cell interior color & thus stays yellow.
    If i select Yes then the cell for that customer is shown like a pink colour.
    This yes no colour change is only for column D

    So far the code in place all works apart from the added code this morning which i require empty cells in column G to be shown as red.
    It might be easier to concentrate on that.

    Column G is the target column.
    The cells with values will be yellow.
    The cells with no values should be Red
    This code should be running all the time so i can see the Red cells at any given time.
    Any cell in column G will always be Red "within my range" UNLESS a value is placed into it of which then it should be yellow

    No value = Red
    With a value = Yellow
    Column G only

    If it helps would you like to see the code that i use to transfer the value from userform to worksheet.
    This then maybe could be edited to add the red cell

    Then the code when i add the date edit it to change the cell yellow

    Please advise
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #14
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell has no value than change interior colour

    Here we go.

    This code adds a date for the selected customer.
    It enters the date into a cell in column G

    Maybe at the same time change the cell to Red & i think this will sort it

    Code:
    Private Sub DateTransferButton_Click()
    'Dantes code
        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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  5. #15
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,585
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If cell has no value than change interior colour

    I need to move on with this now but came across a problem..


    This is the code in use.

    Code:
    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

    When the userform values are transfered to the worksheet i require the cell at column G to become Yellow

    Towards the bottom of the code i added a line which does the trick in respect of the cell changing to Yellow BUT i then lose some functionality of the form,like not in A-Z order etc etc.

    Did i insert it in the correct place ??

    Code:
            Else            sh.Cells(b.Row, "G").Value = CDate(TextBox7.Value)
                sh.Cells(b.Row, "G").Interior.Color = vbYellow
                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
    If i remove theline all functions are fin,put it back then issues arise
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •