VBA code to auto-populate details for certain cells based on certain conditions

[iAmPatch]

Hi,

I'm trying to figure out how to auto-populate details for certain cells based on certain conditions:

1. Columns A, I, L, and Z should auto-populate Date when Column B has information keyed in
2. Column F should auto-populate "Walk In" and Column G should auto-populate "Endorsed to" when Column E has information keyed in
3. Column K should auto-populate "Go" when Column J has information keyed in
4. Column N and AA should auto-populate "No Go" when Column M has information keyed in

I did find a code which auto-populates Date in Column A when information is entered in Column B:

<code class="vb keyword">Private</code> <code class="vb keyword">Sub</code> <code class="vb plain">Worksheet_Change(</code><code class="vb keyword">ByVal</code> <code class="vb plain">Target </code><code class="vb keyword">As</code> <code class="vb plain">Excel.Range)</code>
<code class="vb comments">'Updated by Extendoffice 2017/10/12</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">xRg </code><code class="vb keyword">As</code> <code class="vb plain">Range, xCell </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb spaces">    </code><code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb spaces">    </code><code class="vb keyword">If</code> <code class="vb plain">(Target.Count = 1) </code><code class="vb keyword">Then</code>
<code class="vb spaces">        </code><code class="vb keyword">If</code> <code class="vb plain">(</code><code class="vb keyword">Not</code> <code class="vb plain">Application.Intersect(Target, </code><code class="vb keyword">Me</code><code class="vb plain">.Range(</code><code class="vb string">"B:B"</code><code class="vb plain">)) </code><code class="vb keyword">Is</code> <code class="vb keyword">Nothing</code><code class="vb plain">) </code><code class="vb keyword">Then</code> <code class="vb plain">_</code>
<code class="vb spaces">            </code><code class="vb plain">Target.Offset(0, -1) = </code><code class="vb keyword">Date</code>
<code class="vb spaces">        </code><code class="vb plain">Application.EnableEvents = </code><code class="vb keyword">False</code>
<code class="vb spaces">        </code><code class="vb keyword">Set</code> <code class="vb plain">xRg = Application.Intersect(Target.Dependents, </code><code class="vb keyword">Me</code><code class="vb plain">.Range(</code><code class="vb string">"B:B"</code><code class="vb plain">))</code>
<code class="vb spaces">        </code><code class="vb keyword">If</code> <code class="vb plain">(</code><code class="vb keyword">Not</code> <code class="vb plain">xRg </code><code class="vb keyword">Is</code> <code class="vb keyword">Nothing</code><code class="vb plain">) </code><code class="vb keyword">Then</code>
<code class="vb spaces">            </code><code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">xCell </code><code class="vb keyword">In</code> <code class="vb plain">xRg</code>
<code class="vb spaces">                </code><code class="vb plain">xCell.Offset(0, -1) = </code><code class="vb keyword">Date</code>
<code class="vb spaces">            </code><code class="vb keyword">Next</code>
<code class="vb spaces">        </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">        </code><code class="vb plain">Application.EnableEvents = </code><code class="vb keyword">True</code>
<code class="vb spaces">    </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>

I can't figure out how to replicate it though to meet the conditions listed above

Help please and thank you

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
   Select Case Target.Column
      Case 2
         If Target.Value <> "" Then Intersect(Target.EntireRow, Range("A:A, I:I, L:L,Z:Z")).Value = Date
      Case 5
         If Target.Value <> "" Then Target.Offset(, 1).Resize(, 2).Value = Array("Walk In", "Endorsed to")
      Case 10
         If Target.Value <> "" Then Target.Offset(, 1).Value = "Go"
      Case 13
          If Target.Value <> "" Then Intersect(Target.EntireRow, Range("N:N, AA:AA")).Value = "No Go"
   End Select
Application.EnableEvents = True
End Sub

 

[iAmPatch]

Hi @Fluff

Thanks for your reply. The code works Some questions though to understand the code further ...

I did google a bit and the "Select Case" is equivalent to a nested IF function? This code just makes it easier for VBA and Excel to read the code?

Then with regards to this line of code

If Target.Value <> "" Then Target.Offset(, 1).Resize(, 2).Value = Array("Walk In", "Endorsed to")

how do you read it? I got lost with the .Resize

 

[Fluff]

If the target cell was E5, then the offset takes you to F5 & the Resize(,2) changes that to F5:G5

 

[iAmPatch]

thanks @Fluff

 

[Fluff]

Glad to help