I am trying to create an excel macro which updates the cell alignment, wrap text and borders of cells in each row if a value is entered into one cell within that specific row. For example, if a value is entered into cell A1, then I want the macro to update the wrap text, cell alignment and borders of cells A1:O1. Unfortunately, applying conditional formating to each row within the spreadsheet is rather cumbersome and only will handle updating the cell borders, so I think that a macro that can update all 3 cell formatting elements and dynamically searches the entire worksheet, would be best.
我正在尝试创建一个excel宏,它更新单元格的对齐方式,如果将一个值输入到该特定行中的一个单元格,则在每一行中对单元格的文本和边框进行更新。例如,如果一个值被输入到单元格A1中,那么我希望宏更新单元格A1:O1的包装文本、单元格对齐和边框。不幸的是,对电子表格中的每一行应用条件格式是相当麻烦的,并且只处理更新单元格边界,所以我认为一个可以更新所有3个单元格格式元素并动态搜索整个工作表的宏是最好的。
Thanks for your assistance!
谢谢你的帮助!
1 个解决方案
#1
1
I don't know how you want to trigger this macro, nor what exact formatting you want to apply, but here's what I would do:
我不知道你想怎样触发这个宏,也不知道你想要应用什么格式,但我要做的是:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
With Range(Target, Target.Offset(0,14)
.HorizontalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End with
end if
End Sub
Edit: Add button and Assign Macro window should appear. Select New and put the code there.
编辑:添加按钮和分配宏窗口应该出现。选择New并将代码放在那里。
For Each Target in Range(Cells(1,1), Cells(65536, 1).End(xlUp))
If Target <> "" Then
With Range(Target, Target.Offset(0,14)
.HorizontalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
End If
Next
#1
1
I don't know how you want to trigger this macro, nor what exact formatting you want to apply, but here's what I would do:
我不知道你想怎样触发这个宏,也不知道你想要应用什么格式,但我要做的是:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
With Range(Target, Target.Offset(0,14)
.HorizontalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End with
end if
End Sub
Edit: Add button and Assign Macro window should appear. Select New and put the code there.
编辑:添加按钮和分配宏窗口应该出现。选择New并将代码放在那里。
For Each Target in Range(Cells(1,1), Cells(65536, 1).End(xlUp))
If Target <> "" Then
With Range(Target, Target.Offset(0,14)
.HorizontalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
End If
Next