统计了一个历年库存与库存时间点期间出入库明细的表格,由于行是每一次的出入库明细,因此时间点库存就会按物料相同物料是一样的。
因此增加一列“库存变化”,用于体现每一次出入库后库存的变化。
但是这样库存变化一列数字下来,如果让领导看是不容易区分的。因此打算按物料设置不同颜色用以区分,并且在库存变化上,当库存是统计出来时间点的库存时,设置不同的颜色区分,以方便领导查看。原本以为可以用条件格式,但是发现还是有点困难的,因此使用VBA实现。
因为对数据进行过排序,因此只要根据判断下一行数据与当前行数据是否有区别既可
Sub SameColor()
Dim LastRow As Long
Dim i As Long
Dim Value As Variant, value2 As Variant
Dim ColorValue As Long, ColorValue2 As Long
Worksheets("明细").Select
' 获取最后一行的行号
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
'先进行排序
ActiveSheet.Range("A2:Z" & LastRow).Sort key1:=Range("B2"), order1:=xlAscending, key2:=Range("G2"), order2:=xlAscending, Header:=xlYes
If ActiveSheet.Cells(2, 7) <> "" Then
ActiveSheet.Cells(2, 7) = ""
End If
' 循环检查每一行的值
With ActiveSheet
For i = 3 To LastRow
Value = .Cells(i, 1).Value '列A为要检查的列
value2 = .Cells(i, 7).Value '列G为日期列
' 检查是否与上一行的值相同,不同的话,取新的颜色
If i > 1 And Value <> .Cells(i - 1, 1).Value Then
r = WorksheetFunction.RandBetween(0, 255)
g = WorksheetFunction.RandBetween(0, 255)
b = WorksheetFunction.RandBetween(0, 255)
ColorValue = RGB(r, g, b)
End If
.Cells(i, 1).Interior.Color = ColorValue
' 相同的物料不同的年份,设置成蓝色,表示是22年期末的库存
If i > 1 And Value = .Cells(i + 1, 1).Value And Year(value2) < Year(.Cells(i + 1, 7).Value) And Year(value2) = 2022 Then
ColorValue2 = RGB(65, 105, 225)
.Cells(i, 12).Interior.Color = ColorValue2
End If
'相同的物料不同的年份设紫色
If i > 1 And Value <> .Cells(i + 1, 1).Value And Year(value2) <> Year(.Cells(i + 1, 7).Value) And Year(value2) = 2023 Then
ColorValue2 = RGB(160, 102, 211)
.Cells(i, 12).Interior.Color = ColorValue2
End If
'一些只有一行的物料,设紫色
If i > 1 And Value <> .Cells(i - 1, 1).Value And Value <> .Cells(i + 1, 1).Value And .Cells(i, 12) = .Cells(i, 14) Then
ColorValue2 = RGB(160, 102, 211)
.Cells(i, 12).Interior.Color = ColorValue2
End If
Next i
.Cells(2, 13).Interior.Color = RGB(65, 105, 225)
.Cells(2, 14).Interior.Color = RGB(160, 102, 211)
.Cells(2, 7) = "出入库日期"
End With
End Sub
效果