VBA Code for Changing Text Alignment with Merge Cells

[Tennisguuy]

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

 

[Fluff]

Conditional formatting cannot change the alignment, so the answer is no.

 

[Tennisguuy]

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]

It maybe possible with VBA, but VBA hates merged cells.
What cells need to be changed & when?

 

[Tennisguuy]

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]

You can try this, but not sure if it will work

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]

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]

Did you put the code into the "Exposures" sheet module?

 

[Tennisguuy]

Yes I right clicked on the exposure sheet under the VBAProgject, selected module then inset then I pasted the code. Was that correct.

 

[Fluff]

That's right, are the cells in col A part of the merged cells?