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

[David77]

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:

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

 

[Trevor G]

You can use Conditional Formatting to achieve this.

 

[David77]

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:

Then:

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

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:

Any tips on how to continue?

Would greatly appreciate! Thanks!

 

[Trevor G]

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

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]

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

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

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]

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]

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

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

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]

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]

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

Unfortunately, Conditional Formatting does not affect the default gridlines.

 

[Fluff]

In what way is this different to your other thread Copy pasting cells from one worksheet to another in Excel