VBA Code for Changing Text Alignment with Merge Cells

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows
I am not sure if this can be done and I am not good with writing VBA code. I have a spreadsheet where I have merged some cells. I have instructions in the merged cells. I have it formatted to show the instructions in red and aligned center because that is the option I liked best under merged cells. When the user inputs their text the instructions in red then turns to black but the alignment is still centered.

What I would like to do is leave the current formatting in where the instructions are in red and aligned centered but when the user types over the instructions and it changes it to black and keeps the center alignment I would like to change the alignment from center after they input text in the cell to left alignment.

Can that be done.

I have merged cells in four separate places on the worksheet. The first set merging row 10-14, then 18-22 then 46-50 and 53-57
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Conditional formatting cannot change the alignment, so the answer is no.
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows
Thanks I saw that it could not be changed in conditional formatting but I wasn't sure if you could do it via VBA Code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
It maybe possible with VBA, but VBA hates merged cells.
What cells need to be changed & when?
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

The following cells are currently merged 10-14, then 18-22 then 46-50 and 53-57. I want the alignment to change after the user types over the instructions. It's currently formatted to change from red to black after the user types over the instructions but I just want the alignment to change from center to left alignment after they type over the instructions that are currently in the merged cells.

I am not sure if this help but in merged cells 10-14 there is 411 characters including spaces and in merged cell 18-22 there are 327 characters and in 46-50 there are 340 characters and in 53-57 there are 90 characters.

Fluff if this is hard since VBA doesn't like merged cells I am ok with current set up. I just thought I would give it a try if it was something easy that could be done.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You can try this, but not sure if it will work
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("A10,A18,A46,A53")) Is Nothing Then
      Target.HorizontalAlignment = xlLeft
   End If
End Sub
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Fluff I pasted the above code in the workbook and it didn't work. Just curious. There are a couple of sheets in the workbook. The sheet for this one is named Exposures. Does the name of the worksheet matter to the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Did you put the code into the "Exposures" sheet module?
 

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
559
Office Version
  1. 2016
Platform
  1. Windows
Yes I right clicked on the exposure sheet under the VBAProgject, selected module then inset then I pasted the code. Was that correct.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
That's right, are the cells in col A part of the merged cells?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,673
Messages
5,637,712
Members
416,980
Latest member
wongsafe

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