+ Reply to Thread
Results 1 to 2 of 2

Highlight blank cells in yellow.

Hybrid View

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    San Francisco, Californa
    MS-Off Ver
    Excel 2010
    Posts
    35

    Highlight blank cells in yellow.

    Hello.


    I have the following vba code:

    Sub Test_Hightlight()
    '
    ' Updates transactions TPF& Med Groups
    ' To insert rows across sheets
    '
    ' Keyboard Shortcut: Ctrl+Shift+D
    '
        Dim num As Long, Found As Range, ws As Worksheet
        
        num = Application.InputBox("Number of rows to insert? ", "Insert Rows", Type:=1)
        If num = 0 Then Exit Sub    'User canceled
        
        Application.ScreenUpdating = False
        
    
        For Each ws In Sheets(Array("All_Fund_Series", "MAPMG", "SCPMG", "TSPMG", "TPMG", "HPMG", "GHP", "NWP", "CPMG", "FED", "OPMG"))
            'Set ws = Sheets("All_Fund_Series")
        
            Set Found = ws.Cells.Find(What:="TPF FUND V - SERIES J TOTALS", _
                                      LookIn:=xlFormulas, _
                                      LookAt:=xlPart, _
                                      SearchOrder:=xlByRows, _
                                      SearchDirection:=xlNext, _
                                      MatchCase:=False)
            
            If Not Found Is Nothing Then
                Found.EntireRow.Resize(num).Insert Shift:=xlShiftDown, _
                                        CopyOrigin:=xlFormatFromLeftOrAbove
                With Found.EntireRow.Offset(-num).Resize(num)
                    Found.EntireRow.Offset(-num - 1).Copy Destination:=.Cells
                    Intersect(ws.Range("D:F, J:J,O:O,R:R"), .Cells).ClearContents
                End With
            End If
            
        Next ws
        
        Application.ScreenUpdating = True
        
    End Sub
    After the range of cells ("D:F, J:J,O:O,R:R") are cleared, I would like to highlight these same cells in yellow. What code can be added in to work with the current code to do so?


    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Highlight blank cells in yellow.

                With Found.EntireRow.Offset(-num).Resize(num)
                    Found.EntireRow.Offset(-num - 1).Copy Destination:=.Cells
                    Intersect(ws.Range("D:F, J:J,O:O,R:R"), .Cells).ClearContents
                    Intersect(ws.Range("D:F, J:J,O:O,R:R"), .Cells).interior.colorindex=6
                End With
    If solved remember to mark Thread as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1