|
我自己录制的宏,供参考
Sub 商商工作表格式()
Sheets("商品帐").Select '打开商品帐这个工作表
Application.ScreenUpdating = False '关闭屏幕刷新
Application.EnableEvents = False '禁用应用程序触发事件
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 '打开列全部组
Range("A1:AW3000").Select
Selection.FormatConditions.Delete '清除所选单元格的规则
Range("A1,A4") = "年月日" '写入表头名称
Range("B1,B4") = "价格"
Range("C1,C4") = "序号"
Range("D1,D4") = "商品名称"
Range("E1,E4") = "商品规格"
Range("F1,F4") = "产地"
Range("G1,G4") = "进价"
Range("H1,H4") = "批发价"
Range("I1,I4") = "销售价"
Range("J4") = "单"
Range("J5") = "位"
Range("K4") = "合装"
Range("K5") = "数量"
Range("L4") = "箱装"
Range("L5") = "数量"
Range("M4") = "供货"
Range("M5") = "单位"
Range("N4") = "仓库"
Range("N5") = "数量"
Range("O4") = "门市"
Range("O5") = "数量"
Range("P4") = "进货"
Range("P5") = "数量"
Range("Q4") = "货位"
Range("Q5") = "编号"
Range("R4") = "纸盒"
Range("R5") = "编号"
Range("S4") = "楼上"
Range("S5") = "编号"
Range("T4") = "商品"
Range("T5") = "库存"
Range("U4") = "金额"
Range("V4") = "合计金额"
Range("W4") = "仓"
Range("W5") = "库"
Range("X4") = "门"
Range("X5") = "市"
Range("Y4") = "年月日"
Range("Z4") = "库存"
Range("Z5") = "设置"
Range("AA4") = "库存状态"
Range("AB4") = "辅助列"
Range("AC4") = "查找名称"
Range("AD4") = "重量"
Range("AD5") = "斤/盒"
Range("AE4") = "输入"
Range("AE5") = "重量"
Range("AF4") = "显示"
Range("AF5") = "重量"
Range("AG4") = "产品编号"
Range("AH4") = "备注"
Range("AI4") = "价格"
Range("AI5") = "倍数"
Range("AJ4") = "进进"
Range("AK4") = "进批"
Range("AL4") = "批销"
Range("AJ5:AL5") = "差率"
Range("AM4") = "辅助"
Range("AN4") = "供货合计"
Range("AO4") = "序号"
Range("AP4") = "库存合计"
Range("AQ4") = "辅助"
Range("AR4") = "开孔器"
Range("AR5") = "点数"
Range("AS4") = "辅助"
Range("AT4") = "商品类别"
Range("AU4") = "辅助"
Range("AW4") = "历史"
Range("AW5") = "←记录"
Range("AX4") = "生产单位"
Range("A5:C3000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($A5<>"""",$B5<>"""",$C5<>"""")" '设置条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
With Selection.FormatConditions(1).Borders
.Color = 5287936 '绿色 '设置边框线为1号
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False '条件格式结束
Range("D5:F3000").Select '选择D1:F1
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($D5<>"""",$E5<>"""",$F5<>"""")" '设置条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
Selection.FormatConditions(1).Borders.Color = 12611584 '蓝色(xlLeft) '设置边框线为1号
Range("G5:I3000").Select '选择G1:H1
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($G5<>"""",$H5<>"""",$I5<>"""")" '设置条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
Selection.FormatConditions(1).Borders.Color = 6299648 '深蓝(xlLeft) '设置边框线为1号
Range("K5:M3000").Select '选择K1:M1
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($K5<>"""",$L5<>"""",$M5<>"""")" '设置条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
Selection.FormatConditions(1).Borders.Color = 10498160 '紫色(xlLeft) '设置边框线为1号
Range("N5:P3000").Select '选择N1:P1
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($N5<>"""",$O5<>"""",$P5<>"""")" '设置条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
Selection.FormatConditions(1).Borders.Color = 6299648 '深蓝(xlLeft) '设置边框线为1号
Range("Q5:T3000").Select '选择Q1:T1
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($Q5<>"""",$R5<>"""",$S5<>"""",$T5<>"""")" '设置条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
Selection.FormatConditions(1).Borders.Color = 192 '深色(xlLeft) '设置边框线为1号
Range("J6:J3000").Select '单位列
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(J6))>0" '设置单元格无空值条件格式
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '条件格式
With Selection.FormatConditions(1).Borders(xlTop) '上边框线
.Color = -16776961 '红色
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom) '下边框线
.Color = -16776961 '红色
.Weight = xlThin
End With
Range("U6:Z3000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(U6))>0" '单元格值不为空
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Borders '(xlLeft)
.Color = -1003520 '浅绿色边框线
.Weight = xlThin
End With
Range("AD6:AH3000").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(AD6))>0" '单元格值不为空
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Borders '(xlLeft)
.Color = -1003520 '浅绿色边框线
.Weight = xlThin
End With
Range("AM5:AM3000,AQ5:AQ3000,AS5:AS3000,AU5:AU3000").Select '选择区域
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(AM5))=0" '单元格为空值
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Interior '填充颜色
.PatternColorIndex = xlAutomatic
.Color = 5296274 '绿色
End With
Selection.FormatConditions(1).StopIfTrue = False '条件格式结束
'设置表头条件格式
Range("A1:AU1,A4:AU4").Select '条件格式填充
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($C1<>"""",$D1<>"""",$E1<>"""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式.必须有
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274 '浅绿色
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False '关闭设置
Range("A1:I1,A4,C4:H4,J4:Q4,T4,U4:V4,Y4,Z4,AA4:AD4,AF4,AG4:AL4,AN4:AP4,AR4:AR4,AT4,AW4").Select '设置字体
Selection.Font.Color = 12611584 '蓝色
Range("I4,R4:S4,W4:W4").Select '设置字体
Selection.Font.Color = -16776961 '红色
Range("X4:X4").Select '设置字体
Selection.Font.Color = -11489280 '绿色
Range("I6:I3000,AN1:AP2").Select '设置I6:I3000"字体,
Selection.Font.Color = 10498160 '紫色
Selection.NumberFormatLocal = "0.00;[红色]0.00" '设置I列范围区域,数值= "0.00;[红色]0.00"
'Selection.NumberFormatLocal = "0.00_ " '(= "G/通用格式" 是通用格式)
Range("A1:I1,A4,C4:I4,J4:T4,U4:V4,Y4,Z4,AA4:AD4,AE4:AF4,AG4:AL4,R4:S4,W4:X4,I6:I3000,AN1:AP4,AR4:AR4,AT4,AW4").Select '设置字体
With Selection.Font '设置字体,
.Name = "宋体"
.FontStyle = "加粗"
.Size = 14 '字号
End With
Range("A1:AW4").Select '设置A3:AL4格式
With Selection '自动缩小字体
.HorizontalAlignment = xlCenter '水平对齐,居中
.VerticalAlignment = xlCenter '垂直对齐,居中
.WrapText = False '取消自动换行
.ShrinkToFit = True '自动缩小字体
End With
''End Sub
Range("=$G$5:$AA$3000").Select '在这个区域内设置条件格式。符合条件显示白色字体。
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($B5="""",$I5="""")" '写入公式=OR($B5="",$I5="")
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
Selection.FormatConditions(1).Font.ThemeColor = xlThemeColorDark1 '设置条件格式
Range("A5:AA3000").Select '在这个区域内设置条件格式。符合条件填充颜色。
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($B5="""",$I5="""")" '写入公式=OR($B5="",$I5="")
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Interior '设置条件格式
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6 '6号
.TintAndShade = 0.599963377788629 '颜色
End With
Range("AI5:AL3000").Select '设置条件格式
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR($B5="""",$I5="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Font.Bold = True '加粗字体
Selection.FormatConditions(1).Font.Color = -6279056 '紫色字体
Selection.FormatConditions(1).Interior.ThemeColor = xlThemeColorAccent5 '5号色
Selection.FormatConditions(1).Interior.TintAndShade = 0.799981688894314 '填充浅灰色
Range("AI6:AL3000").Borders.Color = 5287936 '绿色边框线
Range("AI6:AL3000").Borders.Weight = xlThin
Range("AI6:AL3000").Font.Size = 12
Range("AI6:AL3000").Font.Color = 6299648 '深蓝色字体
Range("D6:D3000").Select '单元格的值包涵(处理品)字体紫色并加粗
Selection.FormatConditions.Add Type:=xlTextString, String:="处理品", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = 10498160 '紫色
End With
With Selection.FormatConditions(1).Borders '单元格的值包涵(处理品)边框线红色
.Color = -16776961 '红色边框线
.Weight = xlThin '线号
End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(T6=0)" '当T6=0则D6填充颜色
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Interior
.Color = 5296274 '浅绿色
End With
Selection.FormatConditions.Add Type:=xlTextString, String:="无货", _
TextOperator:=xlContains '单元格的值包涵(无货)字体绿色并加粗
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority '设置条件格式
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = 5287936 '绿色
End With
With Selection.FormatConditions(1).Borders '单元格的值包涵(无货)边框线红色
.Color = -16776961 '红色边框线
.Weight = xlThin '线号
End With
Application.EnableEvents = True '开启应用程序触发事件
Application.ScreenUpdating = True '打开屏幕刷新
Range("A5").Select
End Sub '这个宏于2012年10月29号完成。没有问题。
|
|