Excel VBA Select Case Loop Sub
- by Zack
In my excel file, I have a table setup with formulas.
with Cells from Range("B2:B12"), Range ("D2:D12"), and etc every other row containing the answers to these formulas.
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?
thank you in advance.
