Need Help With Excel Highlight Code! RRS feed

  • Question

  • I will order a pizza (from any place I am able to order online) for anyone who can write me a code to highlight rows via a Command Button in Excel 2010.  I added the command button, but I can't figure out how to program the VBA code correctly.

    I have dates (cells formated to mm/dd/yy) in columns "D" through "P" starting in row 4.  Lower row range can be 300, as I shouldn't ever have more rows than that.  I want the button to execute a command that highlights, in yellow, any row that contains a cell with the current date IF column "Q" is blank.  If column "Q" contains any data, that rown should not be highlighted.

    I told someone at work I could figure this out for them, and I have been failing miserably.  I would GREATLY appreciate any help, and promise to order you a pizza the same day!   Thank you!!!

    Thursday, March 20, 2014 1:35 PM

All replies

  • You don't really need a macro for this - you can use conditional formatting, see below.

    But here is a macro:

    Sub Highlight()
        Dim r As Long
        Dim m As Long
        Dim cel As Range
        Application.ScreenUpdating = False
        m = Range("D:P").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("4:" & m).Interior.ColorIndex = xlColorIndexNone
        For r = 4 To m
            If Range("Q" & r).Value = "" Then
                Set cel = Range("D" & r & ":P" & r).Find(What:=Date, LookIn:=xlValues, LookAt:=xlWhole)
                If Not cel Is Nothing Then
                    Range("A" & r).EntireRow.Interior.Color = vbYellow
                End If
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub

    Using conditional formatting:

    • Select the rows you want to format.
    • I'll assume that the active cell within the selection is in row 4.
    • On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    • Click 'Use a formula to determine which cells to format.
    • Enter the formula     =AND($Q4="",ISNUMBER(MATCH(TODAY(),$D4:$P4,0)))
    • The 4 in this formula is the row of the active cell.
    • Click Format...
    • Activate the Fill tab.
    • Select your preferred highlight color.
    • Click OK twice.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, March 20, 2014 4:51 PM