VBA multiple IF values

Question

The below code works and changes the offset cells when north is entered, i would like to also have it change if its south, west or east but i can seem to find a way to add this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


Set KeyCells = Range("A7:A26")
Set rng = Range("A7:A26")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    For Each cell In rng.Cells
        If cell.Value = "North" Then
            cell.Offset(0, 1).Interior.Color = RGB(0, 255, 0)
            cell.Offset(0, 2).Interior.Color = RGB(0, 255, 0)
            cell.Offset(0, 3).Interior.Color = RGB(0, 255, 0)
        End If
    Next

   End If


End Sub

Do you want to use the same colour or different colours for each direction?? — Gary's Student
– Gary's Student, Commented Apr 17, 2015 at 12:55

Automate This · Accepted Answer · 2015-04-17 13:04:53Z

2

Try using a Select Case instead of If statements for this.

For Each cell In Rng.Cells
    Select Case cell.Value
        Case "North"
            Range(cell.Offset(0, 1), cell.Offset(0, 3)).Interior.Color = RGB(0, 255, 0)
        Case "South"
            Range(cell.Offset(0, 1), cell.Offset(0, 3)).Interior.Color = RGB(0, 155, 0)
        Case "East"
            Range(cell.Offset(0, 1), cell.Offset(0, 3)).Interior.Color = RGB(0, 55, 0)
        Case "West"
            Range(cell.Offset(0, 1), cell.Offset(0, 3)).Interior.Color = RGB(0, 0, 0)
    End Select
Next

answered Apr 17, 2015 at 13:04

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kaz Over a year ago

Never used Select Case before, I will have a look into it. Thanks!

Automate This Over a year ago

Select Case is often ignored by new programmers, but it can make code easier to follow and understand. Enjoy!

Darren Bartrup-Cook Over a year ago

Select...Case Statement (Visual Basic)

FreeMan · Accepted Answer · 2015-04-17 13:02:32Z

Give this a shot. Just update the color assignments as needed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("A7:A26")
Set rng = Range("A7:A26")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
   Is Nothing Then
    For Each cell In rng.Cells
        If cell.Value = "North" Then
            cell.Offset(0, 1).Interior.Color = RGB(0, 255, 0)
            cell.Offset(0, 2).Interior.Color = RGB(0, 255, 0)
            cell.Offset(0, 3).Interior.Color = RGB(0, 255, 0)
        ElseIf cell.Value = "South" Then
            cell.Offset(0, 1).Interior.Color = RGB(255, 0, 0)
            cell.Offset(0, 2).Interior.Color = RGB(255, 0, 0)
            cell.Offset(0, 3).Interior.Color = RGB(255, 0, 0)
        ElseIf cell.Value = "East" Then
            cell.Offset(0, 1).Interior.Color = RGB(0, 0, 255)
            cell.Offset(0, 2).Interior.Color = RGB(0, 0, 255)
            cell.Offset(0, 3).Interior.Color = RGB(0, 0, 255)
        ElseIf cell.Value = "West" Then
            cell.Offset(0, 1).Interior.Color = RGB(0, 255, 255)
            cell.Offset(0, 2).Interior.Color = RGB(0, 255, 255)
            cell.Offset(0, 3).Interior.Color = RGB(0, 255, 255)
        End If
    Next
End If
End Sub

Tony Dallimore · Accepted Answer · 2015-04-17 13:01:58Z

0

Consider:

 If cell.Value = "North" Or cell.Value = "South" Then
        cell.Offset(0, 1).Interior.Color = RGB(0, 255, 0)
        cell.Offset(0, 2).Interior.Color = RGB(0, 255, 0)
        cell.Offset(0, 3).Interior.Color = RGB(0, 255, 0)
 End If

or:

 If cell.Value = "North" Then
        cell.Offset(0, 1).Interior.Color = RGB(0, 255, 0)
        cell.Offset(0, 2).Interior.Color = RGB(0, 255, 0)
        cell.Offset(0, 3).Interior.Color = RGB(0, 255, 0)
 ElseIf cell.Value = "South"
        cell.Offset(0, 1).Interior.Color = RGB(0, 0, 255)
        cell.Offset(0, 2).Interior.Color = RGB(0, 0, 255)
        cell.Offset(0, 3).Interior.Color = RGB(0, 0, 255)
 End If

answered Apr 17, 2015 at 13:01

Tony Dallimore

12.4k7 gold badges35 silver badges63 bronze badges

Collectives™ on Stack Overflow

VBA multiple IF values

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related