Simple VBA code guidance wanted

[percy83]

Hi all VBA experts,

I am trying to use VBA to apply conditional formatting to column “D” but the problem is that all cells are affected by my VBA code and just not only column “D”. I am a total noob at VBA and its probably ridiculously easy to fix it but I still need your help.
Please see code below.
Best regards
Per nilsson

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Range("D:D").Special(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Black"
Cell.Interior.ColorIndex = 1
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2

Case "Blue"
Cell.Interior.ColorIndex = 5
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2

Case "Red"
Cell.Interior.ColorIndex = 3
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2

Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

 

[ajetrumpet]

if you want a humble opinion, conditional formatting is best done through the interface.

 

[crimson_b1ade]

yeah Mr. Trumpet is right. However, I guess if you have more than three conditions you can prolly put your code in an if statement that checks to see if the Target column is the 4th Column (i.e. Column D) and if it is your conditional statements are ran Else nothing happens. Eg:

If Target.Column = 4 Then
        'Do Something
    Else
        'Do Nothing
End If

 

[ajetrumpet]

yeah Mr. Trumpet is right.

I think I'm also right by COMPLETELY reversing your signature. E.g: RIGHT TO LEFT. LOL

 

[crimson_b1ade]

We don't want to go down that path (again). The world would be a far better place without such fairytales. It is ironic how something which would/should be a good thing (or at least one would think) has been twisted by mankind to such a point that it pits different ideologies against each other to the point where our brothers and sisters are willing to take each others lives and to the point where all rational thought is lost.

I think I'm also right by COMPLETELY reversing your signature. E.g: RIGHT TO LEFT. LOL

 

[ajetrumpet]

Good point about killing each other. Not a cool thing. CONVERSATION ENDED (or in today's language, PROGRAM TERMINATED).