Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill column with color
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... -- tpk103 ----------------------------------------------------------------------- tpk1031's Profile: http://www.excelforum.com/member.php...fo&userid=1446 View this thread: http://www.excelforum.com/showthread.php?threadid=26151 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill column with color
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 ---- Let me know how you go Cheers JulieD "tpk1031" wrote in message ... 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 how 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 'red 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 ' yellow 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 'blue 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 'green 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.... -- tpk1031 ------------------------------------------------------------------------ tpk1031's Profile: http://www.excelforum.com/member.php...o&userid=14469 View this thread: http://www.excelforum.com/showthread...hreadid=261518 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing cells within a column based on cell fill color | New Users to Excel | |||
My fill color and font color do not work in Excel Std Edition 2003 | Excel Discussion (Misc queries) | |||
EXCEL MACRO - fill column with color | Excel Programming | |||
Find first and last cells in column that have a certain fill color | Excel Programming | |||
Find first and last cells in column that have a certain fill color | Excel Programming |