If cell has no value than change interior colour

[ipbr21054]

Hi,

The worksheet cells are currently yellow as standard.
The range in question is G9 & continues down the page
Any cell that has no value for its interior colour to then be shown as Red until a value is added then it will be yellow again.

The code i thought that would do it "shown in red below" was added into an existing working code.
The existing code works however my added code doesnt.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 1 Then Exit Sub
        If .Column = 7 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
[COLOR=#ff0000]        If Range("G9:G") = "" Then[/COLOR]
[COLOR=#ff0000]           Range("G9:G").Interior.ColorIndex = 3[/COLOR]
        End If
    End With
End Sub

 

[ipbr21054]

MAIT

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

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()

 

[My Aswer Is This]

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.

 

[ipbr21054]

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

 

[ipbr21054]

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

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

 

[ipbr21054]

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


This is the code in use.

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 ??

        Else            sh.Cells(b.Row, "G").Value = CDate(TextBox7.Value)
[COLOR=#ff0000]            sh.Cells(b.Row, "G").Interior.Color = vbYellow[/COLOR]
            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