[已解决]恳请高手看下这个难题用vba怎么解决，谢谢

qiongmei · 发表于 2011-11-5 17:23

我附件上有一个表，我想实现的功能是当表格中的f列为非空的时候，则对应的a列到n列有边框。例如f1上有数据的时候，则a1到ni有边框，谢谢高手的帮忙。

最佳答案

月排行榜 / 总排行榜

zjdh

2011-11-5 18:36

本帖最后由 zjdh 于 2011-11-5 18:42 编辑

不要这样复杂吧：

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Borders.LineStyle = 0
For I = 1 To Range("F65536").End(3).Row
If Range("F" & I) <> "" Then Range("A" & I & ":N" & I).Borders.LineStyle = 1
Next
End Sub

复制代码

边框线1.rar (12.45 KB, 下载次数: 9)

BAIXINGR · 发表于 2011-11-5 18:14

本帖最后由 BAIXINGR 于 2011-11-5 18:17 编辑

Sub Macro2()
Dim x, y, z As Integer
x = 1
y = Range("f65536").End(xlUp).Row
For z = 1 To y
If Cells(x, 6) <> "" Then
Range(Cells(x, 1), Cells(x, 14)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = xlAutomatic
End With
x = x + 1
Else
Range(Cells(x, 1), Cells(x, 14)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
x = x + 1
End If


Next z
End Sub

zjdh · 发表于 2011-11-5 18:36

本帖最后由 zjdh 于 2011-11-5 18:42 编辑

不要这样复杂吧：

Private Sub Worksheet_Change(ByVal Target As Range)
Cells.Borders.LineStyle = 0
For I = 1 To Range("F65536").End(3).Row
If Range("F" & I) <> "" Then Range("A" & I & ":N" & I).Borders.LineStyle = 1
Next
End Sub

复制代码

边框线1.rar (12.45 KB, 下载次数: 9)

zjdh · 发表于 2011-11-5 18:38

其实可以用数据有效性自动添加更方便：

边框线.rar (12.96 KB, 下载次数: 17)

mqr66 · 发表于 2011-11-5 19:22

还是用zjdh 的方法简单些。

tianti · 发表于 2011-11-5 19:39

真简单

Select · 发表于 2011-11-5 21:14

zjdh 发表于 2011-11-5 18:38
其实可以用数据有效性自动添加更方便：

请问怎么设置的数据有效性呀

zjdh · 发表于 2011-11-6 07:44

回复 Select 的帖子

http://www.excelpx.com/thread-197718-1-1.html

		自动登录	找回密码
密码			注册

[已解决]恳请高手看下这个难题用vba怎么解决，谢谢

评分