I had this working a few days ago but now cannot get it to run

[jeremiah]

jlgwhiz or mike H helped me out with this awhile ago. Well, for lack of a
better excuse I lost some data and had to start over. Now I cannot get this
to work and cannot figure out why. I am getting a variable not defined error.

Sub ConcatColor()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow '<<assumes header row
If range("P" & i).Interior.ColorIndex = 3 Then
range("P" & i) = Cells(i, 7) & Cells(i, 8) & Cells(i, 9)
End If
Next
End Sub

 

[Mike H]

Hi,

It sounds like you have Option Explicit set and therefore all variables must
be dimensioned. Try this

Sub ConcatColor()
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow '<<assumes header row
If Range("P" & i).Interior.ColorIndex = 3 Then
Range("P" & i) = Cells(i, 7) & Cells(i, 8) & Cells(i, 9)
End If
Next
End Sub

 

[jeremiah]

Thanks, I had given that some thought but am not quite familiar enough with
vb to give it a go on my own. Thanks again, it does work now.