excelvbagantt-chart

How to border cells based on where the user click?


I have a Gantt chart in an excel sheet. Everything works, but I would like to improve it by entering a vba code that would put borders around the range of cells in the graph that is concerned by the step on which the user clicks. So the user would click on the cell of the step in the first column and then there would be borders around the colored cells of the graph that are corresponding. What is the code ?

enter image description here


Solution

  • For now, I have this who seem to work, but return numbers in the cells, with conditional formatting it can put a color.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim a, z As Date
    Dim g As String
    Dim f As Integer
    Dim y, x As String
    Dim p, q As String
    Dim h As Integer
    Dim cell As Range
    
    Range("B22:CT41").ClearContents
    On Error GoTo didi
    If Target.Row >= 3 And Target.Row <= 16 Then
    If Target.Column > 50 Then
        x = "CA"
        y = "AY"
    Else
        x = "AD"
        y = "B"
    End If
    
    a = Range(x & Target.Row)
    z = Range(x & Target.Row).Offset(0, 1)
    h = Range(x & Target.Row).Offset(0, 1) - Range(x & Target.Row)
    g = Range(y & Target.Row).Value
    f = CInt(Right(g, 2))
    
    For Each cell In Range(Range("B19"), Range("B19").End(xlToRight))
    If Format(cell, "dd_mm_yyyy") = Format(a, "dd_mm_yyyy") Then
    p = cell.Offset(f + 2, 0).Address
    GoTo dede
    End If
    Next
    
    dede:
    For Each cell In Range(Range("B19"), Range("B19").End(xlToRight))
    If Format(cell, "dd_mm_yyyy") = Format(z, "dd_mm_yyyy") Then
    q = cell.Offset(f + 2, 0).Address
    GoTo dada
    End If
    Next
    
    dada:
    Range(p & ":" & q) = f
    End If
    didi:
    
    End Sub