hi Arthur,

Yay! feeling like a magician is something we all like

Unfortunately, I only have Excel 2003 so I can't open your file. Also, I didn't explain how to use my code (or include the concatenation section for column F)). So here's a modified version, to let me feel like a magician ...
This version relies on you selecting the range C?:F? before you run the macro.

Option Explicit
Sub EgOfCaseStatements()
Dim cll As Range
Dim DesiredLength As Long
For Each cll In Selection
    With cll
        Select Case .Column
            Case 3 'column C
                'is this right?
                DesiredLength = 4 'this may not be right for cells that only have 1 or 2 digits
                'or maybe (currently commented out)...
                'DesiredLength = IIf(Len(.Value) = 3, 4, Len(.Value))
            Case 4 'column D
                DesiredLength = 7
            Case 6 'column F (relies on the "for each" code modifying the LH cells first)
                .Value = .Offset(0, -3).Value & .Offset(0, -2).Value & .Offset(0, -1).Value
                GoTo Continue
            Case Else
                GoTo Continue
        End Select
        .Value = .Value & String(DesiredLength - Len(.Value), ".")
    End With
Continue:
Next cll
End Sub
This can be modified to run for all rows of your data (rather than a selection), assuming your data is continuous with no blank rows, by changing it to:
(also contains some additional coding)

Option explicit 'forces variable declaration (ie Dim blah...)
Sub EgOfCaseStatements()
Dim cll As Range
Dim DesiredLength As Long
Dim LastRow As Long
Dim RangeToChange As Range
'to speed up the macro
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

'defining variables
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
Set RangeToChange = Range("c1:F" & LastRow)

For Each cll In RangeToChange
    With cll
        Select Case .Column
            Case 3 'column C
                'is this right?
                DesiredLength = 4 'this may not be right for cells that only have 1 or 2 digits
                'or maybe (currently commented out)...
                'DesiredLength = IIf(Len(.Value) = 3, 4, Len(.Value))
            Case 4 'column D
                DesiredLength = 7
            Case 6 'column F (relies on the "for each" code modifying the LH cells first)
                .Value = .Offset(0, -3).Value & .Offset(0, -2).Value & .Offset(0, -1).Value
                GoTo Continue
            Case Else
                GoTo Continue
        End Select
        .Value = .Value & String(DesiredLength - Len(.Value), ".")
    End With
Continue:
Next cll
'free memory
Set cll = Nothing 'not sure if this is needed?
Set RangeToChange = Nothing

'to return the settings after speeding up the macro
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub
Right, that's my magic trick (hopefully;-))

Now to answer your questions & help you learn:

Now I need to try to figure out how to make the cells referential and to add a loop.
Define the current (for want of a better word) cell at the start of the loop (I used a "for each" statement), & then use a "with" statement to link the subsequent references to the object/range etc by removing the initial expression & beginning the them with a dot. For example:
CharcountC = Len(Range("D1"))
Color = CharcountC
Select Case Color
    Case 2
        SKUColor = Range("D1").Value & "....."
'could become
with Range("d1")
CharcountC = Len(.value)
Color = CharcountC
Select Case Color
    Case 2
        SKUColor = .Value & "....."
'...
end with
I don't know why this inserting the values into Cell F1. I think I am going to put a DoWhileNot isEmpt(Activecell.offset(0,1)), loop.
This is inserting the info into cell F1 because of:
ActiveCell.Range("F1").Select
ActiveCell.Value = SKUDesign & SKUColor & Size
'which (I think?) can be changed to
Range("F1").Value = SKUDesign & SKUColor & Size
I may be wrong but I think that "dowhilenot" code can be slower than loops using "for i = 1 to lastrow" or "for each cell in rangetocheck". My reasoning is that the latter codes only perform a single calculation whereas the former performs a calculation each time it loops.


When using ActiveCell and Offset selections, how do you define where the selection begins?
Rather than using 
activecell.offset(...
try using either 
Range("c1").offset(...
or 
cells(1,3).offset(...
to define where it starts.

If I want it to start and return values in cell F1 and down until there is an empty cell (in col c), how do write that?
Check out the use of "Lastrow" in my above code. This chooses the last row of column C & then goes upwards until it finds a value & uses that row number.


hth
Rob