moreeg,

Very elegant solution. I have made minor modifications that should make the chart flow correctly based on the presentation of the data. It took me a bit to understand the logic, but after working it out I don't think a purely macro/VBA based solution would work better. I stand by your solution. You have done very good work.

amrinderminhas,

It may be hard to follow moreeg's setup. Let me see if I can make it clearer what is being done.

Your data is in cells B1:N(n), where (n) is the last row of your data with some columns hidden because you don't want them on your chart. Your headings are in cells A1:N1. The first thing you should do is name your data range, B1:N(n). I named the range RollData, you will see why in the VLOOKUP formulas.

In the area P1:W4 moreeg set up formulas to copy the data relevant to the row you click for the Roll No. that you want to chart. This way he can base the chart on the right information.

In P1 we place the Roll No. so that we have it to look up in the data to extract the other information for the chart. This is the first thing the macro does, copy the Roll No. from the cell we click on to P1.

P2:W2 contain the column offsets that we need for VLOOKUP to find the data for the chart. I put them in backwards so that the chart data is in the same form as the table data, so:

P2=1, Q2=14, R2=13, S2=11, T2=9, U2=7, V2=5, W2=3

Below that information I copied the heading information, keeping the same backwards relation to the table, so:

P3: =A1, Q3: =A14, R3: =A13, S3: =A11, T3: =A9, U3: =A7, V3: =A5, W3: =A3

Then, finally, the VLOOKUPs that populate the series data for the chart:

P4: ="Roll No. "&VLOOKUP($P$1,RollData,P2,FALSE)

Q4: =VLOOKUP($P$1,RollData,Q2,FALSE)

R4: =VLOOKUP($P$1,RollData,R2,FALSE)

S4: =VLOOKUP($P$1,RollData,S2,FALSE)

T4: =VLOOKUP($P$1,RollData,T2,FALSE)

U4: =VLOOKUP($P$1,RollData,U2,FALSE)

V4: =VLOOKUP($P$1,RollData,V2,FALSE)

W4: =VLOOKUP($P$1,RollData,W2,FALSE)

Naming the data range makes the VLOOKUP easier to read.

Now, for the macro, I did not change moreeg's code except to make the highlight extend all the way to column N, the last column of the data table:

```
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LastRow = Range("A1").End(xlDown).Row
If Target.Column = 1 And Target.Row > 1 And Target.Row <= LastRow And Target.Cells.Count = 1 Then
Range("P1") = Target
With Range("A2", Range("A1").Offset(LastRow, 13)).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Range(Target, Target.Offset(0, 13)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Cells(Target.Row, Target.Column).Select
End If
End Sub
```

Finally, once this has been done, you can create a chart based on the data in cells Q3:W4 with the heading coming from P4. You will notice that I prepended the "Roll No. " text to the numeric, making the chart easier to understand. Format the chart however you want. If you still have questions about this, feel free to post.

Reports: ·
Posted 3 years ago