Conditional Format

kop442000

Board Regular
Joined
Jul 1, 2004
Messages
179
Hi guys

Is it possibl to merge/unmerge cells as part of a conditional format? Or is it something I would have to do with a macro or something like that?

Thanks for any replies,
Kop.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
You cannot do this via conditional formatting, however, here is an example of some event code which will merge a cell with the cell to its right if you enter the value 10 in it:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = 10 Then
    Range(Target.Address & ":" & Target.Offset(0, 1).Address).MergeCells = True
End If
End Sub
 

kop442000

Board Regular
Joined
Jul 1, 2004
Messages
179
ok well I think I can work with that code to get something near what I want..

Could you just give me the same example as above, but merging if I was entering "10" in the cell D7 rather that in the target cell.

thank you!
 

kop442000

Board Regular
Joined
Jul 1, 2004
Messages
179

ADVERTISEMENT

ok well I think I can work with that code to get something near what I want..

Could you just give me the same example as above, but merging say cell G3 if I was entering "10" in the cell D7?
thank you!
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$7" Then
    If Target.Value = 10 Then
        Range(Target.Address & ":" & Target.Offset(0, 1).Address).MergeCells = True
    End If
End If
End Sub
 

kop442000

Board Regular
Joined
Jul 1, 2004
Messages
179

ADVERTISEMENT

but that still merges D7 right?

I need cell G5 to merge if D7 gets "10" typed in it... could you amend the code for that please?

Thank you,
kop.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,343
Office Version
  1. 365
Platform
  1. Windows
kop


How can you merge 1 cell?:eek:
 

kop442000

Board Regular
Joined
Jul 1, 2004
Messages
179
Because Lewiy originally talked about his code that "will merge a cell with the cell to its right".

So I was just refering to the cell to be merged to it's right.

My actual spreadsheet will require a few different merges but I just want to get a good idea of the code I need.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,707
Messages
5,833,242
Members
430,199
Latest member
Petty queen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top