none
highlight dates when i dont have any targets that i hit. RRS feed

  • Question

  • I have spreadsheet with

    Col B= Date of trade

    Col M = time when my daily target got hit.

    I want to highlight the range of dates when I hit a target and dates that I did not hit a target. in the screenshot below, the blue box shows the range of cells in col B where I have the date 11/27/2015 that I have hit the target in row 42 and col M. so I want to highlight all the dates 11/27/2015

    if you look at yellow box with date 11/29/2015 in col B, I don't have any values in col M. so I want to leave it as is. how can I do it in excel?



    link to the excel file file upload storage
    Saturday, November 26, 2016 12:08 PM

All replies

  • Hi,

    I've made a sample VBA.
    Copy and paste in code of "Sheet3".
    (when open the Book, select a sheet other than "Sheet3", and then select "Sheet3".)
    Private tradeDate As String
    Private lastRow As Long
    Private myRow As Long
    
    ' --- get last row when this sheet activate
    Private Sub Worksheet_Activate()
        lastRow = Range("A1048576").End(xlUp).Row
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect(Target, Range("M2:M1048576")) Is Nothing Then
            Exit Sub
        Else
            ' --- active cell has been changed in column M
            If (ActiveCell.Value = "") Then
                Exit Sub
            Else
                tradeDate _
                    = Year(ActiveCell.Value) & "/" _
                        & Month(ActiveCell.Value) & "/" _
                        & Day(ActiveCell.Value)
                Call prc_HighLight_Date
            End If
        End If
    End Sub
    ' --- HighLighting: selected Cell (column M) and tradeDate (column B)
    Private Sub prc_HighLight_Date()
        Application.ScreenUpdating = False
        Range("B2:B1048576").Interior.ColorIndex = 2
        Range("M2:M1048576").Interior.ColorIndex = 2
        ' ---
        ActiveCell.Interior.ColorIndex = 6     ' -- 6: Yellow
        ' ---
        For myRow = 2 To lastRow
            If (Cells(myRow, 2).Value = tradeDate) Then
                Cells(myRow, 2).Interior.ColorIndex = 6     ' -- 6: Yellow
            End If
        Next
    End Sub
    Regards,
    Ashidacchi
    Sunday, November 27, 2016 3:04 AM
  • Hi,

    Change line If (Cells(myRow, 2).Value = tradeDate) Then  into 

    If (Cells(myRow, 2).Value = CDate(tradeDate)) Then

    Because Cells(myRow, 2).Value is date type and tradeDate is string.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 29, 2016 3:30 AM
    Moderator