achin
09-11-2008, 08:40 PM
Hi,
the following code is not working.
all i want is...
somebody help plz
when i key in "1" in A1 of sheet 1, it'll colored B1:C3 and E2,E3 in blue+bold+white font
when i key in "2" in A1 of sheet 1, it'll colored B1:C3 and E2,E3 in green+bold+white font
when i key in "3" in A1 of sheet 1, it'll colored B1:C3 and E2,E3 in light yellow+bold+black font
Private Sub worksheet_change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'change to suit
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "1"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 5
Case "2"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 10
Case "3"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 6
Case "4"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 46
Case "5"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 45
Case Empty, ""
Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub
the following code is not working.
all i want is...
somebody help plz
when i key in "1" in A1 of sheet 1, it'll colored B1:C3 and E2,E3 in blue+bold+white font
when i key in "2" in A1 of sheet 1, it'll colored B1:C3 and E2,E3 in green+bold+white font
when i key in "3" in A1 of sheet 1, it'll colored B1:C3 and E2,E3 in light yellow+bold+black font
Private Sub worksheet_change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'change to suit
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "1"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 5
Case "2"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 10
Case "3"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 6
Case "4"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 46
Case "5"
Target.Range("B1:C3, E2,E3").Interior.ColorIndex = 45
Case Empty, ""
Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub