Macro for diagonal border based on the input of a cell?

[didge_player]

Hello,
I am fairly new to writing macros for excel. I have played around with programming in C but that is it.
I am wanting to make a macro that reads the value in a cell (C4) and if that value >= 3 then a certain cell lets say (i31) has a diagonal up border. Else nothing.
This is the basic of what I want to do and once I have this I am sure I can modify it and expand on it for the rest of my project.
Any help and thoughts would great.
Thanks
didge_player

 

[Michael M]

Hi and welcome to the forum

Sub MM1()
If Range("C4").Value >= 3 Then
    With Range("I31").Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End If
End Sub

 

[didge_player]

Thanks @Michael M
Is there a way to make it run automatic or do I have to run macros everything I type in a new number in to Cell C4?
Thanks

 

[Michael M]

Copy this code into the Sheet module....NOT a standard or This workbook module

Sub worksheet_change(ByVal target As Range)
Set target = Range("C4")
Range("I31").Borders(xlDiagonalUp).LineStyle = xlNone
If target.Count > 1 Then Exit Sub
If Intersect(target, Range("C4")) Is Nothing Then Exit Sub
If target.Value >= 3 Then
    With Range("I31").Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End If
End Sub

 

[didge_player]

That's great. Thanks so much Michael.
The only problem I am having with it is if I protect my worksheet so that only Cell C4 can be changed it crashes the macro.
Any thoughts?
Thanks
David

 

[Michael M]

Try

Sub worksheet_change(ByVal target As Range)
ActiveSheet.Unprotect
Set target = Range("C4")
Range("I31").Borders(xlDiagonalUp).LineStyle = xlNone
If target.Count > 1 Then Exit Sub
If Intersect(target, Range("C4")) Is Nothing Then Exit Sub
If target.Value >= 3 Then
    With Range("I31").Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End If
ActiveSheet.Protect
End Sub

 

[didge_player]

absolutely perfect.
I have modified what you gave me and it now changes 30 or so different cells to great different patterns based on a single input number.

Thanks so much.
David

 

[Michael M]

pleasure...glad it worked for you...