color cell

[achin]

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

[VBA]
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
[/VBA]

[MaximS]

Should work now:

[VBA]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("Book4").Worksheets("Sheet1").Range("A1")
'change to suit
If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "1"
With Target.Range("B1:C3, E2,E3")
.Interior.ColorIndex = 5
.Font.Bold = True
.Font.ColorIndex = 2
End With
Case "2"
With Target.Range("B1:C3, E2,E3")
.Interior.ColorIndex = 10
.Font.Bold = True
.Font.ColorIndex = 2
End With
Case "3"
With Target.Range("B1:C3, E2,E3")
.Interior.ColorIndex = 6
.Font.Bold = True
.Font.ColorIndex = 0
End With
Case "4"
With Target.Range("B1:C3, E2,E3")
.Interior.ColorIndex = 46
.Font.Bold = True
.Font.ColorIndex = 0
End With
Case "5"
With Target.Range("B1:C3, E2,E3")
.Interior.ColorIndex = 45
.Font.Bold = True
.Font.ColorIndex = 0
End With
Case Empty, ""
Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End Sub
[/VBA]

[anna]

Hi, Maxims..
I've tried to compile with your codes.
But it's promt our an error message:Run Time Error '1004'
It's about the error caused by the Intersect Method.

Any solution on this?

[mdmackillop]

With only three conditions, use Conditional Formatting

[anna]

I have 15 to 20 conditionals here.

[mdmackillop]

[vba]Private Sub worksheet_change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address(0, 0) <> "A1" Then Exit Sub
Select Case Target
Case "1"
Range("B1:C3, E2,E3").Interior.ColorIndex = 5
Case "2"
Range("B1:C3, E2,E3").Interior.ColorIndex = 10
Case "3"
Range("B1:C3, E2,E3").Interior.ColorIndex = 6
Case "4"
Range("B1:C3, E2,E3").Interior.ColorIndex = 46
Case "5"
Range("B1:C3, E2,E3").Interior.ColorIndex = 45
Case Empty, ""
EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select
End Sub

[/vba]

[mdmackillop]

Using this code
[VBA]With Target.Range("B1:C3, E2,E3")[/VBA]
will affect the cells RELATIVE to Target. If Target is not A1 then you may get unexpected results.

[anna]

mdmackillop, the input is on worksheet 1 but output formatting on worksheet 2.

[Simon Lloyd]

Assuming sheet1 is the input sheet something like this should work![VBA]Case "1"
Sheets("Sheet2").Range("B1:C3, E2,E3").Interior.ColorIndex = 5
[/VBA]

[anna]

thank you all very much!

[anna]

how about if i need it to update sheet 3 at the same time when sheet1 is the input sheet ?

[shamsam1]

.[VBA]
Case "1"
Sheets("Sheet2").Range("B1:C3, E2,E3").Interior.ColorIndex = 5
Sheets("Sheet3").Range("B1:C3, E2,E3").Interior.ColorIndex = 5
Sheets("Sheet4").Range("B1:C3, E2,E3").Interior.ColorIndex = 5
.
.
[/VBA]