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

 

[MARK858]

Your ranges are incorrect, you need either a row number after the last G or append a last row variable to it. Then you would need to loop through the range.

Also are you sure that you don't want it happening to the target cell?

 

[ipbr21054]

Hi,
Maybe i did not explain correctly.

Only in column G am i interested in beeing Red in a cell has no value.
So between G9:G1000 say G123 G456 G888 are empty then these cells must be red otherwise yellow.
Currently the last row is 974 but this will get longer every day.

Can you advise how it should be written.

Thanks

 

[MARK858]

I will post something when I get in if you are stuck but I am sure we have shown you how to use last row variables in previous codes?
Also are you sure you want the code amending all the cells each time a target cell is triggered and not just column G on the same row as the target cell?
It just seems a bit strange what you are doing with the code.

I will have a better look at your code when I am in front of a pc.

 

[ipbr21054]

I will look but im also out at present.

Currently in column G for the current range there are say 20 cell of which are empty.
The range will grow each day but also cells will have values added each day.
So i would assume its only ever going to be around the 20 mark.

This is being done as they will start to spread out down the page as time goes on and this is a quick & eay visual indicator.

So basically each time the worksheet is open any cell with no value in column G will be red.

 

[My Aswer Is This]

This should work for the one script you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/6/2019  6:35:15 AM  EDT
If Target.Column = 7 And Target.Row > 9 Then
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Target.Interior.Color = vbRed
End If
End Sub

But I do not understand the script you say already works for you.
What do you want this script to do?

 

[ipbr21054]

MAIT
That codes doesnt change my empty cells in column G red.

Looking at the code in post #1
Originaly there was only the code in black which works.

I then added the code in red thinking it was what i need to change empty cells in column G red but it didnt.
With the code in red added but not workiing for me the black part of it continues to work

 

[My Aswer Is This]

My code does this:
If you have a value in column 7 and then remove the value in column 7 that cell turns red

It only changes the color of the cell when you change the cell value to nothing and only for that cell.

Are you saying you want the script to have to look in all the cells in column 7 each time you change a value in column 7 to nothing?

 

[My Aswer Is This]

And I want to know what the code in Black in Post 1 is suppose to do.

 

[ipbr21054]

I "Think" it just makes all the text on the worksheet Ucase.
Column 1 & 7 & dates