Fill column with color

[tpk1031]

guyz..recently .. i posted a column coloring question.....

i did it...thanx... it works

but ...the function loops many times..... that i dont even know ho
much times it loops...

here is what i did..

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


'x = Target.Row
'Target.Text = UCase(Target.Text)
Dim str As String


If Target.Column = 5 Then
str = UCase(Target.Text)

If str = "M" Or str = "m" Or str = "Merah" Or str = "MERAH" Then

Target.Cells(1, 2).Interior.Color = 0
Target.Cells(1, 2).Interior.ColorIndex = 3 're
color
Target.Value = "Merah"
Exit Sub

ElseIf str = "K" Or str = "k" Or str = "KUNING" Or str = "Kuning
Then

Target.Cells(1, 2).Interior.Color = 0

Target.Cells(1, 2).Interior.ColorIndex = 6 ' yello
color
Target.Value = "Kuning"
Exit Sub

ElseIf str = "B" Or str = "b" Or str = "Biru" Or str = "BIRU" Then


Target.Cells(1, 2).Interior.Color = 0

Target.Cells(1, 2).Interior.ColorIndex = 5 'blu
color
Target.Value = "Biru"
Exit Sub

ElseIf str = "H" Or str = "h" Or str = "Hijau" Or str = "HIJAU
Then

Target.Cells(1, 2).Interior.Color = 0

Target.Cells(1, 2).Interior.ColorIndex = 4 'gree
color
Target.Value = "Hijau"
Exit Sub
End If

Else
Exit Sub
End If

End Sub


guyz...pls help me.... keep looping really waste a lot of time...

 

[JulieD]

Hi

when running your code it only makes a cell change column not the whole
column so i've modified your code to stop the looping and to make the whole
of column 6 change colour. Also you don't need to test for upper & lower
case as the line str=UCase(Target.Text) converts the contents of the cell to
upper case in the code before you test it.
----
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim str As String
Application.EnableEvents = False
If Target.Column = 5 Then
str = UCase(Target.Text)
If str = "M" Or str = "MERAH" Then
Target.Cells(1, 2).Interior.Color = 0
Columns(6).Interior.ColorIndex = 3 'red Color
Target.Value = "Merah"
ElseIf str = "K" Or str = "KUNING" Then
Target.Cells(1, 2).Interior.Color = 0
Columns(6).Interior.ColorIndex = 6 ' yellow Color
Target.Value = "Kuning"
ElseIf str = "B" Or str = "BIRU" Then
Target.Cells(1, 2).Interior.Color = 0
Columns(6).Interior.ColorIndex = 5 'blue Color
Target.Value = "Biru"
ElseIf str = "H" Or str = "HIJAU" Then
Target.Cells(1, 2).Interior.Color = 0
Columns(6).Interior.ColorIndex = 4 'green Color
Target.Value = "Hijau"
End If
End If
Application.EnableEvents = True
End Sub