CODEWITHSUNDEEP
excelvba
Peel
Peel

Reputation: 31

VBA - ALT+Enter (new line) in a Loop

I am writing a macro to create a calendar in a new sheet based on an auto generated report. I have written enough of the macro to create the calendar in a new sheet and highlight any dates that correspond with the report. Now I want to write all of the highlighted dates in one cell at the end of each week. Without vba I'd be able to press "ALT + ENTER". But, the way my current macro runs it just repopulates the cell with the closest highlighted cell to the end rather than using line-brakes. I think I need to incorporate chr(10) somehow but I'm not sure how.

Any help would be greatly appreciated. Here is my current loop:

'find dates in August
Dim augustRng As Range
Set augustRng = Sheets("sheet1").Range("J16:P16")
For Each cell In augustRng
If cell.Interior.ColorIndex = 6 Then
    cell.Select
    Sheets("sheet1").Cells(16, "Q") = ActiveCell.Value
End If
Next cell

Upvotes: 0

Views: 1642

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

Get rid of .Select and append the new cell to the original value of Q16.

If cell.Interior.ColorIndex = 6 Then
    Sheets("sheet1").Cells(16, "Q") = Sheets("sheet1").Cells(16, "Q").Value & Chr(10) & cell.value
End If

Upvotes: 1

Related Questions