If cell has no value than change interior colour

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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?
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
This should work for the one script you want:

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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,563
Office Version
  1. 2013
Platform
  1. Windows
And I want to know what the code in Black in Post 1 is suppose to do.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
I "Think" it just makes all the text on the worksheet Ucase.
Column 1 & 7 & dates
 

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

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
Top