Passing a Color from a Function with a Variable

[Hardware Man]

I have a list of 20 different colors whose RGB values I'd like to hard code into a function. I'd be passing 2 values to the function ("highNum" and "lowNum"). My goal is to calculate the percentage increase between those 2 numbers and return a color based on the result (with the idea being that the larger result gets the brighter color filled into the target cell).

I'm pretty much at the end of my rope in figuring out how to pass the value of the color between the sub and the function, and could really use a hand!

I'm fine with using either the RGB value or the hex value to pass the color (whichever is easiest).

 

[Micron]

So pass 100 and 50 and calculate that 100 is 200% of 50? Or 50 is 50% of 100?
And then what? I don't understand the rest. Examples of inputs and expected results would probably help.

 

[Hardware Man]

"I'm pretty much at the end of my rope in figuring out how to pass the value of the color between the sub and the function, and could really use a hand!"

 

[Micron]

If all you can do is repeat what you've already said then I can't help. Sorry.

 

[Hardware Man]

No need for sarcasm. I repeated the line that mattered because I thought it made things clear.

I do not need help with calculating the percentages. I only need help with passing the color variable.

 

[Eric W]

Passing parameters between modules is a pretty basic feature of VBA. Based on your description, I'd GUESS something like this:

Sub CallingModule()

    highNum = 110
    lowNum = 100
    x = MyFunction(highNum, lowNum)
    Debug.Print x
    
End Sub

Function MyFunction(a, b)

    pct = (a - b) / b
    
    PctArray = Array(0, 0.1, 0.2, 0.3, 0.5)
    RGBArray = Array(RGB(100, 0, 0), RGB(200, 0, 0), RGB(300, 0, 0), RGB(400, 0, 0), RGB(500, 0, 0))
    
    With WorksheetFunction
        MyFunction = .Index(RGBArray, .Match(pct, PctArray))
    End With
    
End Function

If this doesn't help, please show us the sub you have so far, and what your function looks like.

 

[Hardware Man]

Thanks Eric!