我正在尝试对单元格进行颜色编码,以便在运行计算之前警告用户哪个单元格的数据是错误的。当前抛出:
运行时错误438对象不支持属性
当尝试浏览所有数据时。
基本上是创建一个工作表,允许用户在组中输入一些数据,然后基于一些后端数据/限制,给出他们可以/不能做什么的答案。当用户输入数据时,我已经在初始代码中设置了一定程度的错误检查(基本上是尝试并鼓励他们在尝试运行计算之前确保输入的数据是有效的),使用Worksheet_Change子例程可以很好地工作,它可以一次循环所有单元格(在目标范围内),不用担心。
如果我尝试复制它(使用命名区域而不是change函数中的目标单元格),它似乎会做以下两件事之一:它将尝试发送一系列单元格,而不是单个单元格,当它尝试匹配来自“单元格”而不是它应该匹配的单个单元格的数据时,它将失败,或者当它试图给数据着色时,它将抛出438错误。
Private Sub CommandButton1_Click()
Dim ErrorWagonPack As Boolean, ErrorCellsNotEmpty As Boolean, WagonPack As Integer, cel As Range, Target As Range
ErrorWagonPack = False
ErrorCellsNotEmpty = False
Set Target = Application.Range("ConsistInput")
For Each cel In Target 'issue is here somewhere I think. It's related to how the cel is created and as such what properties it can have
Application.EnableEvents = False
WagonPack = 0
If Len(cel.Value) > 0 Then
WagonPack = Application.Index(Application.Range("WagonData"), Application.Match(cel.Value, Application.Range("WagonData").Columns(1), 0), 4)
If cel.Offset(0, 1).Value Mod WagonPack > 0 Then
ErrorWagonPack = True
cel.Font.ColourIndex = RGB(156, 0, 6) '438 error here
cel.Interior.ColourIndex = RGB(255, 199, 206) '438 error here too
End If
End If
Next cel
If ErrorWagonPack = True Or ErrorCellsNotEmpty = True Then
Exit Sub
End If
'calculate stuff
Application.EnableEvents = True
End Sub
理想情况下,我想运行代码,用不正确的数据给导致问题的单元格着色,如果没有抛出任何错误变量,则运行计算,但438错误似乎是症结所在。我几乎愿意打赌,这是一些简单的东西,我只是错过了,但无法弄明白。可能也帮不上忙,我对VBA还是个新手。
发布于 2019-05-29 06:27:41
.ColorIndex
不正确。使用.Color
Private Sub CommandButton1_Click()
Dim ErrorWagonPack As Boolean, ErrorCellsNotEmpty As Boolean, WagonPack As Integer, cel As Range, Target As Range
ErrorWagonPack = False
ErrorCellsNotEmpty = False
Set Target = Application.Range("ConsistInput")
For Each cel In Target 'issue is here somewhere I think. It's related to how the cel is created and as such what properties it can have
Application.EnableEvents = False
WagonPack = 0
If Len(cel.Value) > 0 Then
WagonPack = Application.Index(Application.Range("WagonData"), Application.Match(cel.Value, Application.Range("WagonData").Columns(1), 0), 4)
If cel.Offset(0, 1).Value Mod WagonPack > 0 Then
ErrorWagonPack = True
cel.Font.Color = RGB(156, 0, 6) '438 error here
cel.Interior.Color = RGB(255, 199, 206) '438 error here too
End If
End If
Next cel
If ErrorWagonPack = True Or ErrorCellsNotEmpty = True Then
Exit Sub
End If
'calculate stuff
Application.EnableEvents = True
End Sub
发布于 2019-05-29 06:31:59
.ColorIndex
属性介于1和56之间:
Sub TestMe()
Dim cel As Range
Set cel = Worksheets(1).Range("A1")
cel.Interior.ColorIndex = 1
cel.Font.ColorIndex = 20
End Sub
2个RGB值远大于56:
Sub TestMe()
Debug.Print RGB(156, 0, 6) '393372
Debug.Print RGB(255, 199, 206) '13551615
End Sub
因此,要安全使用的唯一值是56个红色值,介于:
cel.Font.ColorIndex = RGB(56, 0, 0)
和
cel.Font.ColorIndex = RGB(1, 0, 0)
作为替代方案,Range().Font.Color
无需使用ColorIndex
即可支持完整的RGB值:
Range("A1").Font.Color = RGB(100, 20, 255)
https://stackoverflow.com/questions/56350765
复制相似问题