Public Function- highlighting cells with Color (cellcolor)

[brian sullivan]

Greetings Chandoo community,

I'm reaching out; in order to learn the how for developing code that fills cells with color(s). Below, is the VBA Module code for my Public Function Worksheet Function Performance_Message & Worksheet Change Events too.

I have listed the (Module) arguments, declarations, formulas, & Case statement for determining my color fill methodology. Also, I've included notes for what my arguments/variables represent in the code relating to the worksheet.

Relating to filling a cell with a cell color; I'm using cellcolor = blue along with other examples. However, I'm not up to date on the proper coding process .

Thanks.....

Data Notes (Performance Worksheet) MODULE

=Performance_Message(D43,$D$42,E43,$E$42)
VBA variables names in Function Worksheet /Arguments
D43 = Non Weighted Worksheet Report average (Worksheet) / Non PreferredAverage (VBA) single data type
$D$42 = Non Weighted Report Name column Header / NonPreferredAverageName (VBA string variable type)
E43 = Weighted Worksheet Report Average (Worksheet) /PreferredAverage (Single)
$E$E42 = Weighted Worksheet Report averageName (Worksheet)/PreferredAverageName VBA String
Outputp As String Variable (VBA Code) represents returned public function text string output type

=Performance_Message(E43,$E$42,G43,$G$42) same setup here too. Formula public function carries over



VBA MODULE CODE

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 Events

Private Sub Worksheet_Change(ByVal Target As Range)
    SetPerformancecolor (Target)
                  
End Sub

Private Sub SetPerformancecolor(Target As Range)


End Sub

 

[Hui]

Functions cannot change the properties of a cell

So you may also need to use a Worksheet_Change event to make the formatting changes you want

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

You can change the color number to suit or link it to a lookup range etc on a worksheet

 

[brian sullivan]

Fill color questions to VBA color fill related to color change (per attachment).

1. To connect the colors --green/yellow/blue (cellcolor used in this sheet per my case statement to my worksheet function) do I need to ?

A. Do I need to update code in the Module tab sheet ? Please explain

B. For Worksheet event change Sub, can I internally refer to a color range (only writing code on Sheet 1 Report Performance) or am I referring a physical range on the work ?

If I can only write internal code what is the proper coding structure steps to link my Select case Statement colors. Some leads I've found online are using

*Private Sub Worksheet_Change(ByVal Target As Excel.Range)
IF statement (not sure what to put here & if this could work

* Range("A1").Interior.ColorIndex = 0 ---(This references a Range but I'm not sure how I incorporate the select case items in my Module code

C. I'm guessing the changes are made in both the the Change Event & Set performance areas. I'm not sure what the best steps are here.

Thanks....)


VBA Code In Module
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"

Based on original