VBA code or other solution to color cells white if they contain no data

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Hello everybody,

I am currently facing an issue. I would like to create a code that colors my cells with a complete white background and no borders if they contain no data (numbers, letters, signs or anything else).

Please see the following snip:

1596625039126.png


For the blank cells from Cell A135-D135 and below, I would thus like for them to be automatically turned into the same white background as is seen to the right of them because they have no data in their cells.

Is there any way in which I can do this through a VBA code for example?

I would greatly appreciate any assistance I can get! Thank you so much everybody :)

Best regards,
David
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
You can use Conditional Formatting to achieve this.
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
You can use Conditional Formatting to achieve this.

Hello again Trevor,

Thank you so much for your help the last time!

I tried doing that, but it does not work (only if I do it manually by selecting "No Border" under font type and size).

My step currently is:

Mark the area like this:

1596627474689.png


Then:

Conditional formatting --> new rule --> Format only cells that contain: Blanks --> Format and then make it like this:

1596627530119.png


Where I click on "None" and then "Ok".

I also added a white fill for certainty!

However, this doesnt work and my cells still look like this:

1596627597554.png


Any tips on how to continue?

Would greatly appreciate! :) Thanks!
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Hi David, Right click the sheet name at the bottom and select View Code Add this in. You can adjust the range and also the border an thickness

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range
    Dim cell As Range

    Set TargetRange = Range("C3:Q20")  'Adjust the range

    For Each cell In TargetRange
        If cell.Value = "" Then
        cell.Interior.Color = vbWhite
            cell.Borders.Color = vbWhite
            cell.Borders.LineStyle = xlNone
        Else
            cell.Font.Color = vbBlack
            cell.Borders.Color = vbBlack
            cell.Borders.LineStyle = xlThick
        End If
    Next
End Sub
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi David, Right click the sheet name at the bottom and select View Code Add this in. You can adjust the range and also the border an thickness

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range
    Dim cell As Range

    Set TargetRange = Range("C3:Q20")  'Adjust the range

    For Each cell In TargetRange
        If cell.Value = "" Then
        cell.Interior.Color = vbWhite
            cell.Borders.Color = vbWhite
            cell.Borders.LineStyle = xlNone
        Else
            cell.Font.Color = vbBlack
            cell.Borders.Color = vbBlack
            cell.Borders.LineStyle = xlThick
        End If
    Next
End Sub
Hello Trevor,

Thank you so much! I will try this

Where should I insert the code? Because I have a lot of VBA at the moment, and currently "View Code" takes me to my command button at the top (which i dont want to alter):

1596629451257.png


Can I perhaps somehow create a new sheet where I can insert this code? (I have no experience with this, so please bear over with me - thank you very much for the patience! :))

Best regards,
David
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,633
Office Version
  1. 365
Platform
  1. Windows
Where I click on "None" and then "Ok".

I also added a white fill for certainty!

However, this doesnt work and my cells still look like this:
Note that those aren't borders you are seeing, but Excel's default gridlines (gridlines won't print, borders will).
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

H
Hi David, Right click the sheet name at the bottom and select View Code Add this in. You can adjust the range and also the border an thickness

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRange As Range
    Dim cell As Range

    Set TargetRange = Range("C3:Q20")  'Adjust the range

    For Each cell In TargetRange
        If cell.Value = "" Then
        cell.Interior.Color = vbWhite
            cell.Borders.Color = vbWhite
            cell.Borders.LineStyle = xlNone
        Else
            cell.Font.Color = vbBlack
            cell.Borders.Color = vbBlack
            cell.Borders.LineStyle = xlThick
        End If
    Next
End Sub

Hello Trevor,

It seems that your code is working now!

However it gave all my cells the black border for the "Else" value.

Perhaps it is because I have set my cells to obtain data form another sheet (please see photo below):

1596629856160.png


So even through the values in the cells are blank (until I manually enter data in the other sheet) they still contain some text (that is however, not visible in the cell itself seen as it's just a formula).

Might this have an impact on it?

Best regards,
David
 

David77

Board Regular
Joined
Jun 24, 2020
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Note that those aren't borders you are seeing, but Excel's default gridlines (gridlines won't print, borders will).
Hello Joe,

Thank you! Is there a way to do it through conditional formatting then?

Best regards,
David
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,633
Office Version
  1. 365
Platform
  1. Windows
Thank you! Is there a way to do it through conditional formatting then?
Unfortunately, Conditional Formatting does not affect the default gridlines.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,324
Messages
5,658,155
Members
418,430
Latest member
Kizega

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