Format Excel Cells through VB

[Tronsliver]

I would like to fill a cells color with either red, yellow, blue, or green based on the value of the cell. For example here is a function that is called from elsewhere in my program: Based on the value of "p" at the end of the function I would like to color the cells. If p is between 1-5 the color would be green if between 6 - 10 the cell's color would be yellow...etc Thank you




Public Function FindMLR1(x)
Dim y, z, m, p As Integer
p = 0
y = Cells(x, "B&quot.Value
z = Cells(x, "C&quot.Value
If Cells(x, "C&quot.Value = " " Then End

m = (z / y) * 100
If m >= 90 Then
FindMLR1 = 0
Else
Do While m < 90
p = p + 1
z = z + 1
m = (z / y) * 100
Loop
FindMLR1 = p
End If

End Function

 

[SkipVought]

which cell do you want colored? Skip,
metzgsk@voughtaircraft.com

 

[ilses]

Hi Tronsliver,

Depending on how many categories you have besides 1-5 and 6-10 Select Case may be useful (sample assumes it's Cells(x,&quot;B&quot and Cells(x.&quot;C&quot that have to be coloured):

FindMLR1 = p
End If
Select Case p
Case 1 To 5
Range(&quot;B&quot; & x, &quot;C&quot; & x).Interior.ColorIndex = 4
Case 6 To 10
Range(&quot;B&quot; & x, &quot;C&quot; & x).Interior.ColorIndex = 6
End Select

End Function

Alternatively, I'd look for conditional formatting, using p stored as a name without cell reference.

Best regards,

ilses

 

[SkipVought]

I think that you are going to have to do the formatting in a sub and not a function.

Here's what I woulf suggest...
1. x is a value in a cell, correct? If so, then select that cell before running your sub.

2. in the sub declare

Dim x As Range
Set x = ActiveCell

3. Determine where you want the return value of p relative to the active cell.

ActiveCell.Offset(0,5).Value = p

puts the value in Column F if the Active Cell is in Column A

4. Format columns B & C

    Select Case p
        Case 1 To 5
            cIndex = 3
        Case 6 To 10
            cIndex = 3
        Case 11 To 15
            cIndex = 3
        Case 16 To 20
            cIndex = 9
    End Select
    Range(Cells(x.Row, "B"), Cells(x.Row, "C")).Interior.ColorIndex = cIndex

VOLA! Skip,
metzgsk@voughtaircraft.com

 

[Tronsliver]

OK I'm still strugggling with the answers thus far. I believe if I add some additional parts of the program it may be easier to follow. The intent here is to find a percentage based on the value of two cells called y and z. The math is done in the Function called &quot;FindMLR1&quot;. After the return value of the function is ultimitly assigned as the value of (Cell x, F) in the sub, I would like the interior of the cell colored based on that value. For example if &quot;FindMLR1&quot; is between 90 & 100 percent the color of the interior would be colored green. Between 80 & 89 percent the color would be yellow. As you can see the For x loop does this calc 18 times. The intent is after each interation the cell interior will be colored based on the result.

Sub FindDelta()

Dim x As Long

For x = 3 To 21

Cells(x, &quot;F&quot.Value = FindMLR1(x)

End If
Next x
End Sub

Public Function FindMLR1(x)
Dim y, z, m, p As Integer
p = 0
y = Cells(x, &quot;B&quot.Value
z = Cells(x, &quot;C&quot.Value
If Cells(x, &quot;C&quot.Value = &quot; &quot; Then End

m = (z / y) * 100
If m >= 90 Then
FindMLR1 = 0
Else
Do While m < 90
p = p + 1
z = z + 1
m = (z / y) * 100
Loop
FindMLR1 = p
End If

End Function

 

[acron]

How about :

Sub FindDelta()
Dim x As Long
For x = 3 To 21
    With Cells(x, "F")
        .Value = FindMLR1(x)
        Select Case .Value
            Case 90 To 100
                .Interior.ColorIndex = 4
            Case 80 To 89
                .Interior.ColorIndex = 6
            ' etc
        End Select
    End With
Next x
End Sub

AC