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]

Try this:-

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

Regards Mick

 

[gsbelbin]

I know you said not to use CF but I think that is by far the easiest way. Select the range I8:FD31 and open up the CF dialog. Select "Formula is" from the drop down and enter the following formula

=COLUMN(I8)-8=$FF$3

The important bit it to select all the cells at once and enter I8 without any $ signs. That way each cell in the range refers to itself in the Column() function.

Then set the right border as the chosen formatting and that should do it.

 

[AlphaFrog]

If I understand you correctly, I think this could be easily done with Conditional Formatting (CF).

Select cells I8:FD31

Condition 1:
Formula is =AND(I$8=$FF$3, Original CF formula here)
Format background color and border

Condition 2:
Formula is =Original CF formula
Format background color and NO border

 

[picklefactory]

Thanks folks
Mick
I'm doing something daft with that, you have Sub and Private sub at the top which just errors out, I tried removing the top line and changing something in the sheet to activate, but nothing happens. Sorry, I'm obviously being thick.

gsbelbin
You could well be right, used your response and it does the job, my only issue is that the line is not very clear and within the conditional formatting area, there is no option to alter the line weight in the same way as when you format manually. I was hoping that, via VBA, the format would be more controllable. That, of course, is unless someone can tell me otherwise, and that there is a way of customising the format more in the conditional format window.

 

[picklefactory]

Alpha
Thanks also, but unless there's something else I don't know (Highly likely) then I cannot customise the cell format sufficiently in CF. It only produces a very thin line, and I need this to really stand out.
Cheers

 

[AlphaFrog]

Are you willing to put a formula IN the gantt cells?

Cell I9
=IF(I$8=$FF$3, "XXX", "")

Copy the formula down and across.

Or you could use my previous suggestion and change the cell color instead of the border.

 

[MickG]

Your first line should be:-

Private Sub Worksheet_Change(ByVal Target As Range)

There was another line above that when I first posted , which I have removed.
You just need to Right click the sheet tab, select "View Code" , then paste the code in the VB window that appears.
Close the VB window , and just change the value (1 to 152) in cell "FF3", to run the code.
Mick

 

[picklefactory]

Alpha
Thanks again, but unfortunately the bulk of the cells already contain data. The CF route would be great if CF allowed altering the border line thickness, but it doesn't. Is there a way of capturing the border change in VBA, and tweaking it? The column that will change is a known value (FF3), is there a way via VBA to detect the change in that range and alter the border style, maybe?

 

[picklefactory]

Mick
Sorry, not sure what I'm doing stupid now, but nothing appears to be happening at all. I get no errors, but can't see any visible changes at all.
Just for confirmation, this is copied from my VBA window

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
End If
With Range(Cells(8, Target + 8), Cells(31, Target + 8)).Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlHeavy
        .ColorIndex = xlAutomatic
    End With
End Sub

PS
FF3 was initially being changed by a formula (It's a calculated value) but I tried manually inputting a number to FF3 too, neither had any effect.

PPS I tried changing line weight to Heavy in case it just wasn't very clear, still nothing