Hi I am really stuck at the moment with the following problem:

I have a sheet that logs changes in market prices. I am trying to chart the changes in price, if the price goes up a pink cell appears, if it goes down a blue cell appears and continues on to the next column etc, etc.

so my sheet logs price change, and amount of money matched at the price change. I am able to log all the changes and plot the colored blocks, but when i try and merge cells offset from the colored blocks it will not work. if i use the same code as a standard macro and not in worksheet event change it can loop through requested cells and merge relevant cells offset from the cell based on color.

so if a price goes up

If Price <> Sheets("Selection").Cells(4, 4).Value And Price <= Sheets("Selection").Cells(4, 4).Value - (Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value) And Sheets("Selection").Cells(11, iCol - 1).Value = "start" Then 'if the price changes from last price record and move on to next col

Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "l"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
iCol = iCol + 1
i = i + 1
x = x - 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 38


Sheets("Renko").Cells(x + 3, i).Value = Sheets("Selection").Cells(13, iCol - 1).Value

Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Renko").Cells(x, iPriceRange).Value = Sheets("Selection").Cells(10, iCol - 2).Value & sep & Price
Sheets("Renko").Cells(4, i).Value = Sheets("Selection").Cells(13, iCol - 1).Value

if it goes down

ElseIf Price <> Sheets("Selection").Cells(4, 4).Value And Price >= Sheets("Selection").Cells(4, 4).Value + (Sheets("Selection").Cells(1, 10).Value * Sheets("Selection").Cells(6, 4).Value) And Sheets("Selection").Cells(11, iCol - 1).Value = "start" Then 'if the price changes from last price record and move on to next col
Sheets("Selection").Cells(10, iCol).Value = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(11, iCol).Value = "b"
Price = Sheets("Selection").Cells(4, 4).Value
Sheets("Selection").Cells(12, iCol).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Selection").Cells(13, iCol).Value = Sheets("Selection").Cells(5, 4).Value - Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(14, iCol).Value = Sheets("Selection").Cells(13, iCol).Value / Sheets("Selection").Cells(12, iCol - 1).Value
Sheets("Selection").Cells(15, iCol).Value = Sheets("Market").Cells(2, 4).Value
iCol = iCol + 1
i = i + 1
x = x + 1
Sheets("Renko").Cells(x, i).Interior.ColorIndex = 24

Sheets("Renko").Cells(x - 3, i).Value = Sheets("Selection").Cells(13, iCol - 1).Value

Sheets("Renko").Cells(x, iVolMatch).Value = Sheets("Selection").Cells(5, 4).Value
Sheets("Renko").Cells(x, iPriceRange).Value = Sheets("Selection").Cells(10, iCol - 2).Value & sep & Price
Sheets("Renko").Cells(4, i).Value = Sheets("Selection").Cells(13, iCol - 1).Value

i would like to insert code into this worksheet event change that merges cells

Sheets("Renko").Cells(x + 3, i).Value = Sheets("Selection").Cells(13, iCol - 1).Value (x+3 to x+7) if x is colorindex =38

Sheets("Renko").Cells(x - 3, i).Value = Sheets("Selection").Cells(13, iCol - 1).Value (x - 3 to x - 7) if x is colorindex 24

i tired a call merge the cells loop which works if you manually press it (but loops through 100 x 100 cells, not really ideal), but will not work when called in worksheet event change

that code is
Sub Mergethecells()
'
' Mergethecells Macro
' merge cells
'
' Keyboard Shortcut: Ctrl+m
For Z = 50 To 150

For y = 1 To 50
If Sheets("Renko").Cells(Z, y).Interior.ColorIndex = 38 Then
Sheets("Renko").Range(Cells(Z, y).Offset(3, 0), Cells(Z, y).Offset(7, 0)).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
ElseIf Sheets("Renko").Cells(Z, y).Interior.ColorIndex = 24 Then
Sheets("Renko").Range(Cells(Z, y).Offset(-3, 0), Cells(Z, y).Offset(-7, 0)).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 90
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = True
End With
End If
Next y
Next Z

any help is much appreciated!!!!!!