如果一个值已被输入到同一行中的另一个单元格,则Excel宏将更改单元对齐、边框和包装文本。

时间:2022-07-25 20:34:13

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