VBA coding program

[alihadi]

Hi everyone, I need your help in generating three letters randomly which are S, M , L using VBA code. When the letter S is generated one cell has to be colored with yellow, when the letter M is generated two cells have to be merged and colored with green, and when the letter L is generated three cells have to be merged and colored with red. For more details pleae see the attached excel file as an example. Thanks in advance.

[snb]

An .xlsx file can't contain any VBA-code.

[Kenneth Hobs]

It would be more helpful if had posted a more descriptive subject line. I only looked at this since snb responded. He posts some interesting solutions sometimes.

This has limited use. Most coders will avoid using merge as it can be a pain to deal with. In this case, I did not deal with the case where the cell is in a merged range already. You also had a case where you put the value into the last cell of a merge while most would out it in the first cell.

Put this into a Module and it will do as you want for the current active cell.

Sub Test_Random3LettersMergeFillColor()
Random3LettersMergeFillColor ActiveCell
End Sub


Sub Random3LettersMergeFillColor(aCell As Range)
Dim c As Range, a() As String, b(), i As Long

'Exit if more than one cell was passed.
If aCell.Cells.Count > 1 Then Exit Sub

'Set up data and pick random number.
a() = Split("S M L", " ")
b() = Array(vbYellow, vbGreen, vbRed)
i = RBetween(0, 2)
Debug.Print i, aCell.Value

'Exit if no room to merge cells from aCell and up.
If aCell.Row < i + 1 Then Exit Sub

'Set aCell's value, unmerge and merge, and set interior color
aCell.Value2 = a(i)
Set c = Range(aCell, aCell.Offset(-i))
aCell.Interior.ColorIndex = xlColorIndexAutomatic
aCell.UnMerge
c.Merge
c.Interior.Color = b(i)
End Sub


Function RBetween(lowerbound As Long, upperbound As Long) As Long
RBetween = WorksheetFunction.Floor((upperbound - lowerbound + 1) * Rnd + lowerbound, 1)
End Function