Excel VBA选择案例循环子 [英] Excel VBA Select Case Loop Sub

问题描述

在我的excel文件中，我有一个带有公式的表格设置.

In my excel file, I have a table setup with formulas.

包含Range("B2:B12")，Range("D2:D12")等单元格，每隔一行包含这些公式的答案.

with Cells from Range("B2:B12"), Range ("D2:D12"), and etc every other row containing the answers to these formulas.

对于这些单元格(具有公式答案)，我需要应用条件格式，但是我有7个条件，因此我一直在VBA中使用选择大小写"来根据其编号更改其内部背景.我现在在工作表代码中设置了select case函数，而不是它自己的宏

for these cells (with the formula answers), I need to apply conditional formatting, but I have 7 conditions, so I've been using "select case" in VBA to change their interior background based on their number. I have the select case function currently set up within the sheet code, as opposed to it's own macro

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer
    If Not Intersect(Target, Range("B2:L12")) Is Nothing Then
        Select Case Target
            Case 0
                iColor = 2
            Case 0.01 To 0.49
                iColor = 36
            Case 0.5 To 0.99
                iColor = 6
            Case 1 To 1.99
                iColor = 44
            Case 2 To 2.49
                iColor = 45
            Case 2.5 To 2.99
                iColor = 46
            Case 3 To 5
                iColor = 3
        End Select
        Target.Interior.ColorIndex = iColor
    End If
End Sub

但是使用此方法，您必须实际上是在单元格中输入值才能使格式生效.

but using this method, you must be actually entering the value into the cell for the formatting to work.

这就是为什么我想编写一个子例程来作为宏来执行此操作的原因.我可以输入我的数据，让公式起作用，当一切准备就绪时，我可以运行宏并格式化那些特定的单元格.

which is why I want to write a subroutine to to do this as a macro. I can input my data, let the formulas work, and when everything is ready, I can run the macro and format those specific cells.

我想要一种简单的方法来完成此操作，显然我会浪费时间，为每个单元格键入所有案例，但是我发现使用循环会更容易.

I want an easy way to do this, obviously I could waste a load of time, typing out all the cases for every cell, but I figured it'd be easier with a loop.

我将如何编写一个选择大小写循环以更改隔行的特定范围的单元格的格式?

how would I go about writing a select case loop to change the formatting on a a specific range of cells every other row?

先谢谢您

推荐答案

这是一个非常基本的循环，它遍历范围内的所有单元格并设置ColorIndex. (我没有尝试过，但是应该可以使用)

Here is a very basic loop that goes through all cells in a range and sets the ColorIndex. (I did not try it but it should work)

Private Function getColor(ByVal cell As Range) As Integer
    Select Case cell
        Case 0
            getColor = 2: Exit Function
        Case 0.01 To 0.49
            getColor = 36: Exit Function
        Case 0.5 To 0.99
            getColor = 6: Exit Function
        Case 1 To 1.99
            getColor = 44: Exit Function
        Case 2 To 2.49
            getColor = 45: Exit Function
        Case 2.5 To 2.99
            getColor = 46: Exit Function
        Case 3 To 5
            getColor = 3: Exit Function
    End Select
End Function

Private Sub setColor()
Dim area As Range
Dim cell As Range

Set area = Range("B2:L12")
    For Each cell In area.Cells
        cell.Interior.ColorIndex = getColor(cell)
    Next cell
End Sub

编辑:现在可以使用.我忘记添加ColorIndex的Interior infront并将ByRef设置为ByVal. 顺便提一句.请把您的评论作为评论添加到我的答案中.

It works now. I forgot to add Interior infront of ColorIndex and set ByRef to ByVal. Btw. please add your comments as a comment to my answer.

Edit2 :关于更改值时的Errormsg:

Regarding your Errormsg when changing the value:

检测到歧义名称:setColor"

"Ambiguous name detected: setColor"

我想您的worksheet_change中还剩下一些代码.您没有提到要如何运行Sub.

I guess you still have some code left in your worksheet_change. You did not mention how you want to run your Sub.

如果要在worksheet_change上运行它，只需在vba(不是模块)中的工作表中添加代码，然后调用setcolor. 只能有一种setColor ，因此请确保它位于您的模块或工作表中.

If you want to run it on worksheet_change you just need to add the code in the worksheet in vba (not the module) and call setcolor. There can be only one setColor so make sure that it is either in your module or your worksheet.

如果要从模块中运行它，则需要更改

If you want to run it from a module you need to change

Private Sub setColor()

到

Public Sub setColor()

最好在范围的前面添加Worksheetname或ActiveSheet.像这样:

And it would be better to add The worksheetname or ActiveSheet infront of your Range. Like this:

Set area = ActiveSheet.Range("B2:L12")

这篇关于Excel VBA选择案例循环子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！