Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Highlight blank cells

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Highlight blank cells

    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 ??

  2. #2
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    You need to use xlColorIndexNone, not xlNone.
    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    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

  4. #4
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  5. #5
    Board Regular
    Join Date
    Sep 2009
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    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!!

  6. #6
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  7. #7
    Board Regular
    Join Date
    Sep 2009
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    That is amazing!!!!!!!! but if it doesn't find the column header can it not move onto the next column?

  8. #8
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    Is there ever a situation when you'd have a sheet which would be missing one of those headers?

    /AJ
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

  9. #9
    Board Regular
    Join Date
    Sep 2009
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    Hi Adam, unfortunately yes and I can't determine which one would be missing unfortunately, it varies

  10. #10
    Board Regular adam087's Avatar
    Join Date
    Jun 2010
    Location
    So'ton, UK
    Posts
    1,356
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Highlight blank cells

    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
    Adam James
    Enthusiastic Amateur
    Excel 2010 (32-Bit) | Win 7 (64-Bit)

    Track me down on LinkedIn, should you so desire.

    Useful posting advice for which I take no credit:
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker, Boders-Copy-Paste.
    If posting VBA code, please use Code Tags - like this [code]Paste your code here[/code] - more details here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •