0

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

Improve this question
4
  • 4
    You are unlikely to get an answer with how this question is posed. You didn't provide any relevant context to your spreadsheet. Is the Gantt chart in the cells of the sheet or on an actual object? "What is the code" there's no one-size-fits-all solution to programming. Everything must be spelt out to the computer to know what to do, therefore with no spreadsheet context anything anyone gives you would be conceptual and need to be adjusted by you to function. Consider re-writing this question including details of your sheet, what you've tried, and what issues you encountered along the way.
    – Mark S.
    Commented May 29 at 14:57
  • Do you know that macros can be recorded? Just start a recording, do the thing you want to do, stop the recording, and check the recorded macro.
    – Dominique
    Commented May 29 at 14:59
  • Yes, it's in the cells of the sheet. I slightly modified to my taste one of the template available in excel. The list of the steps are in the column C, the gantt graph begin at the line 18.
    – Clara Monspiette
    Commented May 29 at 15:08
  • 2
    You can use the selection_change event to detect when a user clicks on a step title at the top of the sheet - read over from there to get the start/end dates. Then loop over the Gantt chart rows until you find a row where the shading matches the dates.
    – Tim Williams
    Commented May 29 at 16:15

1 Answer 1

Reset to default
0

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
Improve this answer
1
  • Does someone know why there's an error (13 : type mismatch) on the line : f = CInt(Right(g,2)), when I change the order of the steps ?
    – Clara Monspiette
    Commented May 31 at 14:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.