Code to change border of cell range based on cell value

[picklefactory]

Good afternoon folks, hoping for some help, please?
I have a worksheet where I've created a gantt chart via conditional formatting, I won't go into detail now, but for good reason an actual chart is of no use, so I'm creating the same effect via CF and formulae.
Now, I have a row of cells I8:FD8 where each cell contains a sequential number 1-152, and a variable value in cell FF3 that will be a number between 1-152 inclusive. What I want to do, if possible, is based on the value in FF3, I want to change the border of the corresponding cell (Right hand edge only) but also the same change to all the cells below in that particular column from row 8 to row 31.
What I'm looking to achieve is to create a simple line from top to bottom of the 'graph' at a point (Column) determined by a variable value. I could possibly do this also with CF, but to be honest, it's very long winded (At least for me) and my eyes are going square with the amount of that I've done so far on this animal to create the chart data. I'm kinda hoping someone has a nice, easy bit of code that might do this for me?
Any ideas?
Thanks

 

[MickG]

If you place the following (In red) at the bottom of the code
it will tell you if the code is running.
You may have the code in design mode.
If so ,you need to click the Green Triangle next to the Blue square (will change to dark blue) on the tool bar at the top of the Code window.

   End With
Msgbox "Run!!"
End Sub

Mick

 

[picklefactory]

Nope... not in design mode and no message box. Got a meeting now, I'll try rebooting Excel in a bit.
Sorry.

 

[MickG]

Two things
(1) Could you have your "Events " disabled from some previous code, Try running this to enable them.

Application.EnableEvents = True

(2) I've altered the code slightly , Just moved the "End if" to take in all the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "FF3" Then
    With Range("I8:FD31").EntireRow
        .Borders.LineStyle = xlLineStyleNone
        .Interior.ColorIndex = xlNone
    End With
 
With Range(Cells(8, Target + 8), Cells(31, Target + 8)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
End If
MsgBox "Run!!"
End Sub

You could also just run this to see if your "Events" are working, enter as other "Event" code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Hello"
End Sub

 

[picklefactory]

OK Mick, it was exactly as I suspected..... I was doing something dumb. I'd put your code into a module instead of the worksheet code. Works fine.... Doh!

But... it's changing the border formatting on the entire sheet, and not just the selected column/range, the line creation is working fine, but all the formatting on the rest of the sheet is scuppered. Any possible tweaks, maybe?
Sorry for such a dumb error.

 

[picklefactory]

Back again. Fixed that by removing

Borders.LineStyle = xlLineStyleNone

Almost there now, I think. Only other issue is that the coloured bars of the Gantt chart are staying in front of the line your code creates, is there a way of specifying that your border line stays on top of everything. If that can be done I think you've played a blinder, thank you for your time and trouble.
Cheers

 

[picklefactory]

Bugger..... now I understand a bit more of what your code is doing, and also what I hadn't thought of... ie when the value in FF3 changes, how do you undo the previous position of the line (Border format) and replace the original border, hence your line that I removed above.
Problem with that is, the rest of the chart range already has borders formatted in a particular style. I will continue to dabble with your code, as it might be I can get the first section to reformat the remaining area to a suitable style, instead of just removing all borders.
Thanks again for the help, it gives me a big jump forward.

 

[picklefactory]

Problem
I've now finally understood how this works (Told you I was thick). I can sort the remaining sheet formatting issue quite simply, but the bigger issue is that whenever a value is changed in ANY cell, not just FF3, that value creates a border change on the relevant column. EG, FF3 set at 100, works fine, clears all border formatting and adds the required border change to the cells containing 100. BUT, if I alter say, cell D15 to 6, I get a border change on column next to the cell containing a 6, that line stays along with the original, and every time I change any value in any cell other than FF3, I get an additional line without removal of any of the others. Some how need to make this change exclusive to the value in FF3.

 

[MickG]

Are you using the code where I altered the position of the "End If", It will make a difference to the result.
It quite important because the code will run to insert a line, outside you selecting "FF3" if you don't use the altered code. You just have to change its position to the bottom of the code as shown.
Mick

 

[picklefactory]

Right again Mick, I'm rubbish at this (
Just one more thing if your patience can hold out. FF3 is a calculated cell, just a simple formula. The code seems to only work if I alter FF3 directly, as I suppose the formula in FF3 remains constant, only the result changes. The reason I'm not directly changing FF3 is that I have a scaling factor to make the chart self scaling, so the result of the formula in FF3 is simply the scaling factor multiplied by the contents of cell C5, which will be changed directly. I'm sorry if I'm being a pain, but is there a way to make the code function on seeing a change in C5, but using the value in FF3? Or maybe even simpler, use C5 instead of FF3 and just multiply that by the scaling factor (FF2) within the code to provide the same resulting number between 1-152?

 

[picklefactory]

Woohoo!
Sussed it. Had a dabble and I think I fluked it. I'm pretty rubbish at VBA (Don't use it very often, so don't have chance to learn), but I managed to do a simple multiplication in the code which is doing the job now.
Thanks to MickG and also the other guys who also made good suggestions.
Thank you all for your time and patience.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ScaleFactor As Single
ScaleFactor = Range("FF2").Value
If Target.Address(0, 0) = "C5" Then
    With Range("I8:FD31").EntireRow
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = xlNone
    End With
 
With Range(Cells(8, (Target * ScaleFactor) + 8), Cells(31, (Target * ScaleFactor) + 8)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = 3
    End With
End If

End Sub