• Status: Solved
• Priority: Medium
• Security: Public
• Views: 281

# Conditional statement to fill in cells?

Hello Experts,

I have a group of terms that need to be plugged in to specific cells based on text from another cell. (Image Attached)

I think the image would make more sense than my trying to explain it.  But the pseudocode could be something like the following or probably a switch statement.
``````if(cellPlay=”Sweep”)
{
cellColor=”Blue”;
}
else if (cellPlay=”Dive”)
{
cellColor=”Red”);
}
else if(cellPlay=”Toss”)
{
cellColor=”Green”);
}
else if(cellPlay=”Slant”)
{
cellColor=”White”;
}
else if(cellPlay=”Keeper”)
{
cellColor=”Gold”
}
``````
if-else.png
0
Modifier1000
• 4
• 2
• 2
• +1
5 Solutions

Commented:
There's no switch statement in Excel but what you could use is a Select Case.

This code will go down A1:A10, check the value in each cell and put the appropriate term in the cell to the right.
``````Dim I As Long
Dim cellplay As Range
Dim cellcolor As String

For I = 1 To 10

Set cellplay = Range("A" & I).Value

cellcolor = ""

Select Case cellplay

Case "Sweep"

cellcolor = "Blue"

Case cellplay = "Dive"

cellcolor = "Red"

Case "Toss"

cellcolor = "Green"

Case "Slant"

cellcolor = "White"

Case "Keeper"

cellcolor = "Gold"

End Select

cellplay.Offset(, 1).Value = cellcolor

Next I
``````
0

Commented:
You are not far off .. if the first empty color cell was A2 ... making the play value C2 (adjust for your case).  In A2 put the formula
=if(C2="Sweep","Blue",if(C2="Dive","Red",if(C2="Toss","Green",if(C2="Slant","White",if(C2="Keeper","Gold","")))))
and copy down the column.
0

Commented:
Here's a VBA that uses Select Case to insert the value. Note that I assume the header line starts on row 1. 'Color' is on column A and 'Play' is on column C.
sew

``````Sub InsertColorText()
Dim intSrcRow As Integer
Dim strColumn As String
Dim strData As String

strColumn = "C"

For intSrcRow = 2 To ActiveSheet.Cells(Cells.Rows.Count, strColumn).End(xlUp).Row
strData = ActiveSheet.Cells(intSrcRow, strColumn).Value

Select Case strData
Case "Sweep":
ActiveSheet.Cells(intSrcRow, "A").Value = "Blue"
Case "Dive":
ActiveSheet.Cells(intSrcRow, "A").Value = "Red"
Case "Toss":
ActiveSheet.Cells(intSrcRow, "A").Value = "Green"
Case "Slant":
ActiveSheet.Cells(intSrcRow, "A").Value = "White"
Case "Keeper":
ActiveSheet.Cells(intSrcRow, "A").Value = "Gold"
End Select
Next
End Sub
``````
0

Commented:
If you want a formula:

=LOOKUP(A1, {"Sweep";"Dive";"Toss";"Slant";"Keeper"}, {"Blue";"Red";"Green";"White";"Gold"})

Enter this in the cell next to Sweep/Dive etc, change the cell reference A1 to that cell's reference and copy down.
0

Commented:
Here's my code updated to reflect the columns in the image.
``````Dim I As Long
Dim cellplay As Range
Dim cellcolor As String

For I = 1 To 10

Set cellplay = Range("C" & I).Value

cellcolor = ""

Select Case cellplay

Case "Sweep"

cellcolor = "Blue"

Case cellplay = "Dive"

cellcolor = "Red"

Case "Toss"

cellcolor = "Green"

Case "Slant"

cellcolor = "White"

Case "Keeper"

cellcolor = "Gold"

End Select

cellplay.Offset(, -2).Value = cellcolor

Next I
``````
0

Author Commented:
Ok, here is a DUMB question in regards to VBA.  I code in PHP, Javascript and such.  But how do I run this code in VBA?

I'm not a VBA person at all.
0

Commented:
Hit Alt-F8, type a new macro name and select Create. Paste the code to the subroutine.
To run macro from your worksheet, hit Alt-F8, select the macro you created and click Run.
sew
0

Commented:
Or if you want the code to run automatically whenever a value changes in a specified range, eg C2:C11 you could use a worksheet change event.

To use this code right click the tab of the worksheet you have the data on, select View Code... and paste the code in the code window.
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim cellcolor As String
Dim cellplay As Range

If Intersect(Range("C2:C11"), Target) Is Nothing Then Exit Sub

If Target.Cells.Count > 1 Then Exit Sub

Set cellplay = Target

Select Case cellplay.Value

Case "Sweep"

cellcolor = "Blue"

Case "Dive"

cellcolor = "Red"

Case "Toss"

cellcolor = "Green"

Case "Slant"

cellcolor = "White"

Case "Keeper"

cellcolor = "Gold"

End Select

Application.EnableEvents = False
cellplay.Offset(, -2).Value = cellcolor
Application.EnableEvents = True

End Sub
``````
By the way, you've not actually mentioned what range(s) are involved here.

I've 'guessed' C2:C11, is that right?
0

Author Commented:
Thanks to al!
0

## Featured Post

• 4
• 2
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.