Worksheet_Change

[carlleese24]

Hi

Hoping someone can help me I am trying to create 2 worksheet change private sub functions in my excel sheet but then I have then as the same name an error occurs

compile error ambiguous name detected worksheet change and also if I change the name the code does not work is there any way to solve this problem here is my code for it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
Dim rng2 As Range
' Only look at single cell changes

If Target.Count > 1 Then Exit Sub
Set rng = Range("L22:L1000")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Offset(0, 0) = 5 Then
Target.Offset(0, 0).Interior.ColorIndex = 51
End If


End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
Dim rng2 As Range
' Only look at single cell changes

If Target.Count > 1 Then Exit Sub
Set rng = Range("M22:M1000")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Offset(0, 0) = 10 Then
Target.Offset(0, 0).Interior.ColorIndex = 51
End If

Carl

 

[GTO]

You can only have one Worksheet_Change event for the sheet. Unless I misread, your two subs are identical anyays. What is it that you are trying to do?

Mark

 

[carlleese24]

Hi GTO


I am trying to change the cell colour if this range L22:L1000 = 5 but sometimes in range L22:L1000 value of 10 will be in this range but I do not want it to change colours if this happens for this range.


also change colours for this range m22:m1000 = 10 but sometimes in range m22:m1000 value of 5 will be in this range but I do not want it to change colours if this happens for this range.


Carl

 

[Diablo II]

try conditional formattimg.

 

[carlleese24]

Hi Diablo

There Conditional formatting only has 3 conditions I have 6 on each range

I am trying to change the cell colour if this range L22:L1000 = 5 15 25 35 45 55

also change colours for this range m22:m1000 = 10 20 30 40 50 60

 

[carlleese24]

I have done some sort of code for it but it doesn't work


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
Dim rng2 As Range
' Only look at single cell changes

Set rng = Range("L22:L1000")
' Only look at that range

If Target.Offset(0, 0) = 5 Or Target.Offset(0, 0) = 15 Or Target.Offset(0, 0) = 25 Or Target.Offset(0, 0) = 35 Or Target.Offset(0, 0) = 45 Or Target.Offset(0, 0) = 55 Then
Target.Offset(0, 0).Interior.ColorIndex = 51
End If


Set rng2 = Range("m22:m1000")

If Target.Offset(0, 0) = 10 Or Target.Offset(0, 0) = 20 Or Target.Offset(0, 0) = 30 Or Target.Offset(0, 0) = 40 Or Target.Offset(0, 0) = 50 Or Target.Offset(0, 0) = 60 Then
Target.Offset(0, 0).Interior.ColorIndex = 51
End If

End Sub

 

[lenze]

Have alook at this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 22 Then Exit Sub
Dim myCol As Integer
Select Case Target.Column
    Case 12 'Column "L"
        Select Case Target
            Case 5, 15, 25, 35, 45, 55: myCol = 51
            Case Else:
        End Select
    Case 13 'Column "M"
        Select Case Target
            Case 10, 20, 30, 40, 50, 60: myCol = 51
            Case Else:
        End Select
End Select
Target.Interior.ColorIndes = myCol
End Sub

lenze

 

[Diablo II]

here is one code that i have played with its not much but you can play with it.

Sub ConditionalFormatting()
   With Selection
       .FormatConditions.Delete
       .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=MOD(ROW(),2)=0"
       With .FormatConditions(1).Interior
           .ColorIndex = 15
           .PatternColorIndex = 15
           .Pattern = xlGray50
       End With
   End With
End Sub