Highlight blank cells

excel_2009

Active Member
Joined
Sep 14, 2009
Messages
318
Hi excel gurus,

I have the following code which does not work properly, it should highlight cells in one column that are blank:

Code:
Sub BorderForNonEmpty()
    Dim myRange As Range
    Set myRange = Sheets("Inventory").Range("A1:A888")


    'clear all color
    myRange.Interior.ColorIndex = xlNone


    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
End Sub

Please can someone help :)??
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You need to use xlColorIndexNone, not xlNone.
Rich (BB code):
Option Explicit

Public Sub BorderForNonEmpty()
    
    Dim myRange As Range
    Set myRange = Sheets("Inventory").Range("A1:A888")

    'clear all color
    myRange.Interior.ColorIndex = xlColorIndexNone

    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

End Sub

AJ
 
Upvote 0
Thank you Adam, it's perfect!

I do have one question though, would it be possible to adapt the code so that it works based on the column header e.g my worksheet has around 50 columns, I only need to highlight the blank cells in the columns that are labelled with the following: Title, Description, Image. The range length is based on column A i.e. this column will always be populated with data so it could be 100 rows long therefore the remainder columns will also be 100 rows long (so that no other empty cell is filled in beyond this, does this make sense?)

i hope this makes sense :S
 
Upvote 0
Sure. Assuming that those columns are always going to be consistent (IE, Description is always column D, for example), then you could use this code. Change the column refs to suit...
Code:
Option Explicit

Public Sub BorderForNonEmpty()
    
    Dim myRange As Range
    Dim lastRow As Long
    
    lastRow = Sheets("Inventory").Range("A999999").End(xlUp).Row
    Set myRange = Sheets("Inventory").Range("A1:A" & lastRow & ",D1:D" & lastRow & ",F1:F" & lastRow)

    'clear all color
    myRange.Interior.ColorIndex = xlColorIndexNone

    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

End Sub

Hope that helps.


/AJ
 
Upvote 0
Hi Adam,

Thank you so much for your reply, unfortunately the column headers are not fixed, is there a workaround due to this?

Thank you so much!! :)
 
Upvote 0
This will work, although fail if it does not find one or more of the required headers...
Code:
Option Explicit

Public Sub BorderForNonEmpty()
    
    Dim myRange As Range
    Dim lastRow As Long
    
    Dim headTitle As Long
    Dim headImage As Long
    Dim headDescr As Long
    
    With Sheets("Inventory")
        headTitle = Application.WorksheetFunction.Match("Title", .Rows("1:1"), 0)
        headImage = Application.WorksheetFunction.Match("Image", .Rows("1:1"), 0)
        headDescr = Application.WorksheetFunction.Match("Description", .Rows("1:1"), 0)
    End With
    
    lastRow = Sheets("Inventory").Range("A999999").End(xlUp).Row
    With Sheets("Inventory")
        Set myRange = .Range(.Cells(1, headTitle), .Cells(lastRow, headTitle))
        Set myRange = Union(myRange, .Range(.Cells(1, headImage), .Cells(lastRow, headImage)))
        Set myRange = Union(myRange, .Range(.Cells(1, headDescr), .Cells(lastRow, headDescr)))
    End With

    'clear all color
    myRange.Interior.ColorIndex = xlColorIndexNone

    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

End Sub

How's that for you?

/AJ
 
Upvote 0
That is amazing!!!!!!!! but if it doesn't find the column header can it not move onto the next column?
 
Upvote 0
Is there ever a situation when you'd have a sheet which would be missing one of those headers?

/AJ
 
Upvote 0
Hi Adam, unfortunately yes and I can't determine which one would be missing unfortunately, it varies
 
Upvote 0
OK. I got it to work with this, but there may well be a better or more elegant way to do it...
Code:
Option Explicit

Public Sub BorderForNonEmpty()
    
    Dim myRange As Range
    Dim lastRow As Long
    
    Dim headTitle As Long
    Dim headImage As Long
    Dim headDescr As Long
    
    headTitle = 0
    headImage = 0
    headDescr = 0
    Set myRange = Sheets("Inventory").Range("A1")
    
    On Error Resume Next
    With Application.WorksheetFunction
        headTitle = .Match("Title", Sheets("Inventory").Rows("1:1"), 0)
        headImage = .Match("Image", Sheets("Inventory").Rows("1:1"), 0)
        headDescr = .Match("Description", Sheets("Inventory").Rows("1:1"), 0)
        Debug.Print headTitle & " " & headImage & " " & headDescr
    End With
    On Error GoTo 0
    
    lastRow = Sheets("Inventory").Range("A999999").End(xlUp).Row
    With Sheets("Inventory")
        If headTitle > 0 Then Set myRange = .Range(.Cells(1, headTitle), .Cells(lastRow, headTitle))
        If headImage > 0 Then Set myRange = Union(myRange, .Range(.Cells(1, headImage), .Cells(lastRow, headImage)))
        If headDescr > 0 Then Set myRange = Union(myRange, .Range(.Cells(1, headDescr), .Cells(lastRow, headDescr)))
    End With

    'clear all color
    myRange.Interior.ColorIndex = xlColorIndexNone

    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6

End Sub

/AJ
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top