将单元格(n)的颜色索引设置为等于单元格(n-1)的颜色索引 [英] Setting cell (n) Color Index equal to cell (n - 1) Color Index

问题描述

我从这个宏中得到了一个奇怪的输出.宏应使用上面的颜色填充空白单元格，从而创建一个颜色块.即使 Debug.Print 显示相同的 ColorIndex 数字，结果也不是我所期望的.

I am getting a weird output from this macro. The macro should fill the blank cells with the color above creating a block of colors. The result is not what I expected even though Debug.Print shows the same ColorIndex number.

知道这里发生了什么吗?

Option Explicit

Sub Crayon()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim MyCell As Range

For Each MyCell In ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    If MyCell.Interior.ColorIndex = -4142 Then
        If MyCell.Offset(-1).Interior.ColorIndex <> -4142 Then
            MyCell.Interior.ColorIndex = MyCell.Offset(-1).Interior.ColorIndex
        End If
    End If
Next MyCell

End Sub

我习惯跟随宏在照片上产生 G列，该列显示了由上面的宏 Crayon 产生的每个单元的颜色索引.我不确定如何用相同的颜色索引来解释不匹配的颜色的输出.

I used to following macro to produce Column G on the photo, which shows color index for each cell produced by the above macro Crayon. I am not sure how to explain the output of mismatched colors with identical color indexes.

Sub Index_Output()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i

For i = 2 To 17
    ws.Range("G" & i) = ws.Range("C" & i).Interior.ColorIndex
Next i

End Sub

P.S.这是我尝试回答这问题，因为这是我如何学习/练习VBA的方法:)

P.S. this was my attempt to answer this question as this is how I learn/practice VBA :)

推荐答案

不同的颜色归因于 ColorIndex 的性质，该颜色为

The varying colors is due to the nature of ColorIndex, which as MSDN notes represents the index of a color in a palette. Colors from different palettes will have the same index number, so use Color instead of ColorIndex.

在最里面的 If ... End IF 中将 ColorIndex 更改为 Color .

Change ColorIndex to Color within the innermost If...End IF.

Sub Crayon()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim MyCell As Range

For Each MyCell In ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    If MyCell.Interior.ColorIndex = xlNone Then
        If MyCell.Offset(-1).Interior.ColorIndex <> xlNone Then
            MyCell.Interior.Color = MyCell.Offset(-1).Interior.Color
        End If
    End If
Next MyCell

End Sub

这篇关于将单元格(n)的颜色索引设置为等于单元格(n-1)的颜色索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！