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

David77

Board Regular
Joined
Jun 24, 2020
Messages
85
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Trevor G

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

David77

Board Regular
Joined
Jun 24, 2020
Messages
85
Office Version
365
Platform
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,649
Office Version
2016
Platform
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
85
Office Version
365
Platform
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
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
54,092
Office Version
365
Platform
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
85
Office Version
365
Platform
Windows
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
85
Office Version
365
Platform
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
54,092
Office Version
365
Platform
Windows
Thank you! Is there a way to do it through conditional formatting then?
Unfortunately, Conditional Formatting does not affect the default gridlines.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,933
Messages
5,508,195
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top