Excel VBA仅在断点模式下执行突出显示单元格

Question

我为Excel VBA写了一个非常简单的宏。它基本上是将整个工作表部分复制，然后在用户选择的单元格中加4。

我的挑战在于最后一步 - 我想要突出显示我添加4的单元格。 我选择这个单元格并粘贴值:

With Progression.Cells(CatSearch, CurrentColumn)
    .Value = LF
    .Interior.ColorIndex = 37
End With

问题在于它改变了单元格的值，但没有改变颜色。有趣的是，在断点模式下它可以工作，但在运行整个宏时却不行。

Public Sub BuildFootageProgression()

Dim Category As Byte
Dim Working As Workbook
Dim Progression As Worksheet

Set Working = ActiveWorkbook
Set Progression = Working.Sheets(1)


Category = InputBox("Enter Category Number of Category to Gain Next Four Feet", "Category Decision Box")
''Asks which category should gain the next four feet in a popup box


Dim CatSearch As Integer
Dim InputResult As String
CatSearch = 1


'Start by verifying that the input category is in the list, and identifies the row # of that category and saves to variable "Cat Search"

Do Until Sheet1.Cells(CatSearch, 1).Value = Category
    CatSearch = CatSearch + 1

    If CatSearch > 10000 Then
        InputResult = InputBox("The previously entered category number could not be found. Enter Category Number of Category to Gain Next Four Feet.", "Category Decision Box")

        If InputResult = vbNullString Then
            Exit Sub
        Else
            Category = InputResult
            CatSearch = 1
        End If
    End If
Loop


'This section is to find the first blank column and identify the 12 columns we will be working with
If Sheet1.Cells(CatSearch, 1).Value = Category Then

    Dim CurrentColumn As Integer
        CurrentColumn = 1

    Do Until IsEmpty(Cells(1, CurrentColumn))
    CurrentColumn = CurrentColumn + 1
    Loop


End If

'Function to copy formula from previous section to current section
Dim previous As Range
Set previous = Range(Sheet1.Cells(1, CurrentColumn - 11), Sheet1.Cells(100, CurrentColumn - 1))
previous.Copy (Sheet1.Cells(1, CurrentColumn))

Dim Current As Range
Set Current = Range(Sheet1.Cells(1, CurrentColumn + 1), Sheet1.Cells(100, CurrentColumn + 10))
Current.Columns.AutoFit

'Unhighlights all of the cells
For Each c In Current.Cells
    c.Interior.ColorIndex = 2
Next

'Adds four feet to chosen category
Dim LF As Integer
LF = Sheet1.Cells(CatSearch, CurrentColumn).Value
LF = LF + 4

With Progression.Cells(CatSearch, CurrentColumn)
    .Value = LF
    .Interior.ColorIndex = 37
End With


'Takes Null Values and Makes the Cells Blank
Cells.Replace "#N/A", "", xlWhole

End Sub

- Holly Kat

---

我不熟悉CatSearch / CurrentColumn。这两个东西是否会查看最近更改的单元格？我唯一在执行断点模式与“常规运行”时得到不同结果的经历是，断点模式有时会保持不同的工作表/单元格处于“活动”状态，因为它会“激活”代码所在的工作表下面的模块。 - Grade 'Eh' Bacon

你上面的代码片段与你发布的代码不匹配。在发布的代码中，你没有设置颜色。 - Jeremy

好的，我已经添加了它！ - Holly Kat

哪个版本的Excel？我记得在旧版本中ColorIndex存在一些问题。 - SQL Police

Answer 1

Sub Color()
  Dim myRange As Range
  Set myRange = Range("B1")
  myRange.Interior.ColorIndex = 22
End Sub

- justkrys