CODEWITHSUNDEEP
excelconcatenationhighlightvba
user3914368
user3914368

Reputation: 5

Concatenating Columns

So I am trying to concatenate Column A cells with Column C cells, but the problem is that I only want to concatenate the two columns if and only if the column C cell is highlighted. Some cells in column C are highlighted (every other but it may not be like that all of the time). So say we start from A2 and C2, if C2 is highlighted then we concatenate A2 with C2 and leave the change in C2. Then move on to the next row and if C3 is not highlighted then we skip that and check if C4 is highlighted. If C4 data is highlighted then we concatenate A4 with C4. So we just continue that as a loop onto the last row that has information.

So far this is what I have: 

Sub ConcatAandC()

Dim lRow As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lRow
Cells(i, 3) = Cells(i, 1) & "- " & Cells(i, 3)
Next i

End Sub

As you can see, this concatenates all of the rows. I am only targeting the rows where Column C cells are highlighted.

My second problem is that since Column A consist of dates, I only want the month and day to show up. I tried to just change the formatting of Column A hoping that when it copies the date from column A to column C it'll only show the month and the day but unfortunately it wasn't that simple. For instance, Cell A2 has 8/1/2014, when it concatenates with C2 it'll only be 8/1 and Cell C data. I also thought about creating a code where it scans through column C to search for "/2014" and deletes it but I have no idea where to start with that.

So I would greatly appreciate it if someone can give me ideas on how to do this. I am also new to VBA so please bear with me!

Upvotes: 0

Views: 352

Answers (1)

lowak
lowak

Reputation: 1284

This will loop through Column C and check if the cell has Yellow color and if it does then it will concatenate it.

Sub ConcatAandC()
    Dim lRow As Long, i As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            '~~> Check If cell in Col C is yellow
            If .Range("C" & i).Interior.Color = 65535 Then _
            .Range("C" & i).Value = Format(.Range("A" & i).Value, "MM/DD") & _
                                    " - " & .Range("C" & i).Value
        Next i
    End With
End Sub

Upvotes: 1

Related Questions