percy4
04-15-2009, 03:49 AM
Hi VBA experts,
I've got this code and I wan't to set my range to two columns (J & M) but I can't get it to work. Perferrably I would like to set the range like (k2:K300 etc.) to make the code run as smooth as possible. Pls help me.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
Set Rng1 = Intersect(Range("J2:J300"), Target)
If Not Rng1 Is Nothing Then
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Cell.Font.ColorIndex = 1
Cell.Font.Name = "arial"
Case -3000 To 0
Cell.Interior.ColorIndex = 3
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case 1 To 14
Cell.Interior.ColorIndex = 6
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 15 To 1000
Cell.Interior.ColorIndex = 10
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Cell.Font.Name = "arial narrow"
Cell.Font.ColorIndex = 1
End Select
Next
End If
End Sub
I've got this code and I wan't to set my range to two columns (J & M) but I can't get it to work. Perferrably I would like to set the range like (k2:K300 etc.) to make the code run as smooth as possible. Pls help me.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
Set Rng1 = Intersect(Range("J2:J300"), Target)
If Not Rng1 Is Nothing Then
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Cell.Font.ColorIndex = 1
Cell.Font.Name = "arial"
Case -3000 To 0
Cell.Interior.ColorIndex = 3
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case 1 To 14
Cell.Interior.ColorIndex = 6
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 1
Case 15 To 1000
Cell.Interior.ColorIndex = 10
Cell.Font.Name = "arial narrow"
Cell.Font.Bold = True
Cell.Font.ColorIndex = 2
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Cell.Font.Name = "arial narrow"
Cell.Font.ColorIndex = 1
End Select
Next
End If
End Sub