worksheet change event and multiple target cells

[zrx1200]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "h" Then
    Target.Interior.Color = vbGreen
ElseIf Target.Value = "s" Then
        Target.Interior.Color = vbRed
ElseIf Target.Value = "v" Then
        Target.Interior.Color = vbBlue

End If

I've got my coding working for individual cells but fails when multiple cells are selected. What can be done to fix this.

So, for example I select range A5:A9 and type h all cells of this range should have an h and blue.


Thanks

 

[zrx1200]

I think I see now. I thought you were saying that all cells in that range were being populated with the value (i.e. through a copy/paste or auto-fill), when in actuality only the first cell in the selected range is being updated, and you want the macro to populate the rest of the cells in the selected range with the same value and apply the color formatting.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myValue As String
    Dim cell As Range
    
    Application.EnableEvents = False
            
'   Capture value entered in first cell
    myValue = Target.Cells(1, 1).Value
            
'   Set all cells in selection equal to value
    If Selection.Count > 1 Then Selection = myValue

'   Apply appropriate formatting
    Select Case myValue
        Case "h"
            Selection.Interior.Color = vbGreen
        Case "s"
            Selection.Interior.Color = vbRed
        Case "v"
            Selection.Interior.Color = vbBlue
    End Select
    
    Application.EnableEvents = True
            
End Sub

yes, you are on same page now Joe.

only issue we have now is single selection, try it. it drops down one row?

I assume because of this > If Selection.Count > 1 Then Selection = myValue
tried 0, -1

Also why the applic on/off I assume so it will not run selection change event?

 

[Joe4]

only issue we have now is single selection, try it. it drops down one row?

Yes, in that instance the Selected cell and the Target cell are not the same, because hitting enter moves the selected cell down one.

This modification should accommodate that.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myValue As String
    Dim myRange As Range
    
    Application.EnableEvents = False
            
'   Capture value entered in first cell
    myValue = Target.Cells(1, 1).Value
            
'   Capture entire selected range
    If Selection.Count > 1 Then
        Set myRange = Selection
    Else
        Set myRange = Target
    End If
    
'   Set entire range equal to value from first cell
    myRange = myValue

'   Apply appropriate formatting
    Select Case myValue
        Case "h"
            myRange.Interior.Color = vbGreen
        Case "s"
            myRange.Interior.Color = vbRed
        Case "v"
            myRange.Interior.Color = vbBlue
    End Select
    
    Application.EnableEvents = True
            
End Sub

Also why the applic on/off I assume so it will not run selection change event?

Well, if you have other Event Procedures, that is one reason. But the main reason is so that the event procedure calls itself and get caught in an endless loop.
Think about it. This event procedure runs whenever a cell is updated. What do you think happens if the code itself updates cells?

 

[zrx1200]

Yes, in that instance the Selected cell and the Target cell are not the same, because hitting enter moves the selected cell down one.

This modification should accommodate that.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim myValue As String
    Dim myRange As Range
    
    Application.EnableEvents = False
            
'   Capture value entered in first cell
    myValue = Target.Cells(1, 1).Value
            
'   Capture entire selected range
    If Selection.Count > 1 Then
        Set myRange = Selection
    Else
        Set myRange = Target
    End If
    
'   Set entire range equal to value from first cell
    myRange = myValue

'   Apply appropriate formatting
    Select Case myValue
        Case "h"
            myRange.Interior.Color = vbGreen
        Case "s"
            myRange.Interior.Color = vbRed
        Case "v"
            myRange.Interior.Color = vbBlue
    End Select
    
    Application.EnableEvents = True
            
End Sub

Well, if you have other Event Procedures, that is one reason. But the main reason is so that the event procedure calls itself and get caught in an endless loop.
Think about it. This event procedure runs whenever a cell is updated. What do you think happens if the code itself updates cells?

Great works perfect now! Thanks for helping!

just trying to work out a few things in my head yet. I may be back for clarification if google doesn't help (grin)

thank you Joe