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

 

[Joe4]

Loop through each cell in the Target range.
Also, I like to use Case statements in situations like this. I think they a little cleaner and easier to read and maintain (instead of a whole bunch of ElseIf statements):

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range
    
    For Each cell In Target
        Select Case cell.Value
            Case "h"
                cell.Interior.Color = vbGreen
            Case "s"
                cell.Interior.Color = vbRed
            Case "v"
                cell.Interior.Color = vbBlue
        End Select
    Next cell
    
End Sub

 

[zrx1200]

Loop through each cell in the Target range.
Also, I like to use Case statements in situations like this. I think they a little cleaner and easier to read and maintain (instead of a whole bunch of ElseIf statements):

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range
    
    For Each cell In Target
        Select Case cell.Value
            Case "h"
                cell.Interior.Color = vbGreen
            Case "s"
                cell.Interior.Color = vbRed
            Case "v"
                cell.Interior.Color = vbBlue
        End Select
    Next cell
    
End Sub

Failed, as its not picking up the "range" just first cell of the range selected that's it?

 

[Joe4]

Failed, as its not picking up the "range" just first cell of the range selected that's it?

I don't understand.
Can you tell me exactly what you are doing that should be triggering this event procedure to run.

I think you may be mixing up your events here.
Note that Worksheet_Event procedures (which is what you have here), only runs when cells are updated manually or by copy/paste.
If you want it to run when cells are selected, you would use a Worksheet_SelectionChange event procedure, not a Worksheet_Event one.

 

[zrx1200]

ADVERTISEMENT

I don't understand.
Can you tell me exactly what you are doing that should be triggering this event procedure to run.

I think you may be mixing up your events here.
Note that Worksheet_Event procedures (which is what you have here), only runs when cells are updated manually or by copy/paste.
If you want it to run when cells are selected, you would use a Worksheet_SelectionChange event procedure, not a Worksheet_Event one.

Ok, I think I see whats going on for the change event.

The case is selected and runs once as programmed. I need it to complete for all additional cells selected. Not having to input the letter and enter key for each selected cell.

The selectionchange event makes no improvement as it stands now.

Does this help?

 

[Joe4]

The case is selected and runs once as programmed. I need it to complete for all additional cells selected. Not having to input the letter and enter key for each selected cell.

It does, for every single cell that is changed.

I have tested it out and it works for me.
Please explain to me in detail EXACTLY what you are doing. Walk me through an actual example of what you are doing so I can exactly duplicate what you are doing.

 

[zrx1200]

ADVERTISEMENT

It does, for every single cell that is changed.

I have tested it out and it works for me.
Please explain to me in detail EXACTLY what you are doing. Walk me through an actual example of what you are doing so I can exactly duplicate what you are doing.

Yes I see using the selectionchange event with cells already populated with h it turns all back color when selected. not what im after.

Ok, let me try....

cells or range of cells are blank you then select the example range b3:g3 .
select b3:g3 and type h
all cells in selected range must then have an h and corresponding back color.

 

[Joe4]

select b3:g3 and type h
all cells in selected range must then have an h and corresponding back color.

There's the problem - doing that only puts an "h" in the first cell in the range, not in all cells in that range.
Try it on a sheet without any VBA, and you will see that doing that only enters an "h" in cell b3 and not in any of the rest.

So, since b3 is the only cell being updated with an h, it makes sense that it is the only see being highlighted, because none of the other cells meet the requirements.

 

[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

Try again, maybe expanded on above.

you have blank worksheet. You select one cell say A1 move cursor over 4 cells A5 which now gives you a range of A1:A5 highlighted (selected). You type h in (which is the A1) of this still selected range.

Now here is what I wish to happen A2 thru A5 will fill with h and back color blue or whatever in case statements Joe laid out.

Note: The select cell or cells will behave as above and the select cell will not have letter in it, user is putting this.

I believe this is a better explanation I hope?

 

[Joe4]

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