Search code examples
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 conditionnal formating 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