VBA- Worksheet Public Function with a select case statement (Coloring cells per conditions)

[briansullivanuconn]

Below is the worksheet setup for a worksheet (Dragdown) function I've created attempting to show a color range. My question is how do I perform a function where my worksheet cell colors change based on the (Select Case Statement) below tied to my current Work_Sheet Change /Set Performance

MY Select Case statement uses cellcolor , but my BY val target (change event function uses mycolor). I need to pull the colors from the select case into by value code. I'm missing the links here.





The current code I have below only generates one color for all cells


Peromance_Message (Work sheet function setup with variable arguments)


Non Preferred Average Name ($D$42 - Text String) column header
Non Preferred Average (D43- Single) data below (Data begins)
Preferred Average Name (E$42- Text String) column header Preferred Average (E43- Single) data below (data begins)
Column to right of D & E (I drop down Performance_Message Formula function)


MODULE


Public Function Performance_Message(NonPreferredAvg As Single _ , NonPreferredAvgname As String _ , PreferredAvg As Single _ , PreferredAvgname As String _ , Optional Outputtype As String _ ) As Variant


Dim performancemessage As String
Dim averagedifference As Single
Dim stravgdif As String
Dim cellcolor As String


averagedifference = Abs(NonPreferredAvg - PreferredAvg)
stravgdif = FormatPercent(averagedifference, 2)


Select Case PreferredAvg
Case Is < NonPreferredAvg
performancemessage = PreferredAvgname & " Is " & stravgdif & " Less Than " & NonPreferredAvgname
cellcolor = "green"


Case Is = NonPreferredAvg
performancemessage = PreferredAvgname & " Equals " & NonPreferredAvgname
cellcolor = "yellow"


Case Is > NonPreferredAvg
performancemessage = PreferredAvgname & " Is " & stravgdif & " Greater Than " & NonPreferredAvgname
cellcolor = "blue"


Case Else
performancemessage = "Something Bad Happened"




End Select


If Outputtype = "color" Then
Performance_Message = cellcolor
Else
Performance_Message = performancemessage


End If
End Function


WORKSHEET


Private Sub Worksheet_Change(ByVal Target As Range) Dim myColor As Double myColor = 135 Call SetPerformancecolor(Target, myColor)


End Sub


Private Sub SetPerformancecolor(Target As Range, myColor As Double) Target.Interior.Color = myColor End Sub

 

[mrmmickle1]

Welcome to the board.

If you are using words for colors I think you need to proceed them by "vb".....i.e.


vbYellow.....vbBlue.....vbGreen.....Etc.

What exactly are you trying to do? Are you trying to color cells based on a range of numbers they fall into? What do you mean by drag down function?