Updating Multiple Columns Based on Cell Value

[reberryjr]

I'm curious, is there a "smarter" way to code this? It works, but it's lengthy so I'm wondering if there's a better way.

For i = 2 To WDLR
    If Range("AP" & i).Value = "DS | Purple | N" Then
        Range("AQ" & i).Value = "N/A"
        Range("AR" & i).Value = "2"
        Range("AS" & i).Value = "N3"
        Range("AT" & i).Value = "Y"
        Range("AU" & i).Value = "Y"
        Range("AV" & i).Value = Range("J" & i).Value
        Range("AW" & i).Value = "N/A"
        Range("AX" & i).Value = "N/A"
        Range("AY" & i).Value = "E"
        Range("AZ" & i).Value = "E"
    ElseIf Range("AP" & i).Value = "DS | Purple | Y" Then
        Range("AQ" & i).Value = "N/A"
        Range("AR" & i).Value = "0"
        Range("AS" & i).Value = "I3"
        Range("AT" & i).Value = "NULL"
        Range("AU" & i).Value = "R"
        Range("AV" & i).Value = Range("J" & i).Value
        Range("AW" & i).Value = "N/A"
        Range("AX" & i).Value = "N/A"
        Range("AY" & i).Value = "No Update"
        Range("AZ" & i).Value = "No Update"
    ElseIf Range("AP" & i).Value = "DS | Green | N" Then
        Range("AQ" & i).Value = "0"
        Range("AR" & i).Value = "2"
        Range("AS" & i).Value = "N3"
        Range("AT" & i).Value = "Y"
        Range("AU" & i).Value = "N/A"
        Range("AV" & i).Value = "N/A"
        Range("AW" & i).Value = "11"
        Range("AX" & i).Value = Range("J" & i).Value
        Range("AY" & i).Value = "F"
        Range("AZ" & i).Value = "F"
    ElseIf Range("AP" & i).Value = "DS | Green | Y" Then
        Range("AQ" & i).Value = "0"
        Range("AR" & i).Value = "0"
        Range("AS" & i).Value = "I3"
        Range("AT" & i).Value = "NULL"
        Range("AU" & i).Value = "N/A"
        Range("AV" & i).Value = "N/A"
        Range("AW" & i).Value = "11"
        Range("AX" & i).Value = Range("J" & i).Value
        Range("AY" & i).Value = "No Update"
        Range("AZ" & i).Value = "No Update"
    ElseIf Range("AP" & i).Value = "DS | Yellow | N" Then
        Range("AQ" & i).Value = "0"
        Range("AR" & i).Value = "2"
        Range("AS" & i).Value = "N3"
        Range("AT" & i).Value = "Y"
        Range("AU" & i).Value = "N/A"
        Range("AV" & i).Value = "N/A"
        Range("AW" & i).Value = "12"
        Range("AX" & i).Value = Range("J" & i).Value
        Range("AY" & i).Value = "G"
        Range("AZ" & i).Value = "G"
    ElseIf Range("AP" & i).Value = "DS | Yellow | Y" Then
        Range("AQ" & i).Value = "0"
        Range("AR" & i).Value = "0"
        Range("AS" & i).Value = "I3"
        Range("AT" & i).Value = "NULL"
        Range("AU" & i).Value = "N/A"
        Range("AV" & i).Value = "N/A"
        Range("AW" & i).Value = "12"
        Range("AX" & i).Value = Range("J" & i).Value
        Range("AY" & i).Value = "No Update"
        Range("AZ" & i).Value = "No Update"
    ElseIf Range("AP" & i).Value = "DS | Blue | N" Then
        Range("AQ" & i).Value = "0"
        Range("AR" & i).Value = "2"
        Range("AS" & i).Value = "N3"
        Range("AT" & i).Value = "Y"
        Range("AU" & i).Value = "N/A"
        Range("AV" & i).Value = "N/A"
        Range("AW" & i).Value = "13"
        Range("AX" & i).Value = Range("J" & i).Value
        Range("AY" & i).Value = "H"
        Range("AZ" & i).Value = "H"
    ElseIf Range("AP" & i).Value = "DS | Blue | Y" Then
        Range("AQ" & i).Value = "0"
        Range("AR" & i).Value = "0"
        Range("AS" & i).Value = "I3"
        Range("AT" & i).Value = "NULL"
        Range("AU" & i).Value = "N/A"
        Range("AV" & i).Value = "N/A"
        Range("AW" & i).Value = "13"
        Range("AX" & i).Value = Range("J" & i).Value
        Range("AY" & i).Value = "No Update"
        Range("AZ" & i).Value = "No Update"
    End If
Next i

 

[DanteAmor]

You can put all the values on a "helper" sheet. For example:

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K
1	TEXT	VALUE1	VALUE2	VALUE3	VALUE4	VALUE5	VALUE6	VALUE7	VALUE8	VALUE9	VALUE10
2	DS | Purple | N	N/A	2	N3	Y	Y	Range("J" & i)	N/A	N/A	E	E
3	DS | Purple | Y	N/A	0	I3	NULL	R	Range("J" & i)	N/A	N/A	No Update	No Update
4	DS | Green | N	0	2	N3	Y	N/A	N/A	11	Range("J" & i)	F	F
5	DS | Green | Y	0	0	I3	NULL	N/A	N/A	11	Range("J" & i)	No Update	No Update
6	DS | Yellow | N	0	2	N3	Y	N/A	N/A	12	Range("J" & i)	G	G
7	DS | Yellow | Y	0	0	I3	NULL	N/A	N/A	12	Range("J" & i)	No Update	No Update
8	DS | Blue | N	0	2	N3	Y	N/A	N/A	13	Range("J" & i)	H	H
9	DS | Blue | Y	0	0	I3	NULL	N/A	N/A	13	Range("J" & i)	No Update	No Update
aux

Run the following code on the sheet where you are going to update the information.

Sub code_2()
  Dim i As Long
  Dim sh As Worksheet
  Dim f As Range, r As Range
  Dim col As String
 
  Set sh = Sheets("aux")
  For i = 2 To Range("AP" & Rows.Count).End(3).Row
    Set f = sh.Range("A:A").Find(Range("AP" & i).Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      With Range("AQ" & i).Resize(1, 10)
        .Value = f.Offset(0, 1).Resize(1, 10).Value
        Set r = .Find("Range", , xlValues, xlPart, , , False)
        If Not r Is Nothing Then
          col = Split(r.Value, """")(1)
          r.Value = Range(col & i).Value
        End If
      End With
    End If
  Next
End Sub

This way you can add colors in the "aux" sheet and the macro will remain the same.