Solved HEX Colour Assignment Based On Dynamic Range (1 Viewer)

[LGDGlen]

Hi All

trying to figure something out wondering if anyone had any pointers/ideas on best way to achieve the following?

I have some temperature data for dynamic period, as in can be any number of days, that i'd like to assign colours to based on relationship to min and max temperature.

I have stored all the temperatures into a sorted array so i know what the min and max are and can assign RED (255/0/0) to last array element and BLUE (0/0/255)to the first array element.

What i need to do is assign HEX colour values to the remaining temps so the middle is WHITE.

So as an example:

DailyTemps: (9.5,9.6,10,11.2,12.4,14.5,19.2)

9.5 = 0,0,255
9.6
10
11.2 = 255,255,255
12.4
14.5
19.2 = 255,0,0

I want to programmatically assign values to ones in between blue->white and then white->red

This is a very simplified example as there could be a couple of hundred temperatures in the array but i'm assuming the process would be the same no matter the size of the dataset

i hope this makes sense, i know its a bit of a weird question and not something majorly urgent as its more a nice to have for something but just trying to figure it out and can't and would like to understand if its possible (i mean its easy to do in excel i know to assign a colour map to a dataset so i'm just trying to achieve something similar)

thanks in advance

Glen

 

[LGDGlen]

looking at a HEX colour picker:

RED: 255,0,0 to WHITE: 255,255,255 as long as the 2nd and 3rd hex digits are the same and go up in regular intervals then it will be a RED getting lighter till it gets to white

BLUE: 0,0,255 to WHITE: 255,255,255 as long as 1st and 2nd hex digits are the same and go up in regular intervals then it will be a BLUE getting lighter till it gets to white

so i guess i just divide 255 by the number of intervals between lowest and middle and just add that to the 2 HEX numbers that need to change

i'm hoping that thats what i need to do, just wondering if someone smarter than me can chime in and correct me if i am wrong or confirm

 

[isladogs]

Not a direct answer to your question but you may find this utility helpful:

Colour Converter

This demonstrates two different methods for selecting and converting colours between the three different formats used by Access: OLE, RGB & HEX

www.isladogs.co.uk

Also not quite clear where the colours will fit in your range but why not use the standard colours starting with blue for cold through green, yellow, orange to red for hot?

 

[CJ_London]

these links may help

Let’s do Color and Math

Understanding and applying the formulas of color conversion

donatbalipapp.medium.com

VBA Code to Lighten or Darken Fill Colors in Excel

I've been on a mission the past two weeks to craft a pair of macros to lighten and dark a cell fill color.

www.thespreadsheetguru.com

think you might want to set colors to temperature ranges - if in one dataset the highest temperature is 20 and in another 50, do you want both to have the same shade of red? As implied by your post

 

[arnelgp]

you put the temp Value, Color pair to a table.
you then dlookup() its (Hex, Long, RGB) color using
the temp Value.

 

[LGDGlen]

@isladogs @CJ_London @arnelgp thank you for your responses, i resolved the issue by doing the following:

• Add all temperature values to an unsorted arraylist
• Removed duplicates from the arraylist and sorted it
• Counted the number of elements in the array list to find mid element
• Divided 255 by the number of elements to the mid point to get my RGB increment value
• Looped through the arraylist elements
  • if element is in 0-mid point then add temperature to dictionary adding RGB increment value to blue parts to get from blue to white
  • if element is in mid point - end point then add temperature to dictionary subtracting RGB increment value to red parts to get from white to red
• When outputting temperature look it up in dictionary to find corresponding RGB value to output

Hope that makes sense, probably over complicated matters so may go back and retool the code, didn't want to use a table as its only temporary and just to resolve a visual view of some data for a specific user, it was kind of a "can i do it" more than "totally necessary"

 

[isladogs]

You might also want to look at my Colour Conditions - Mendip Data Systems example

 

[arnelgp]

maybe you can make use of this.
extracted from Excel Conditional format (using three colors).

 

[LGDGlen]

@arnelgp i'll download and take a look and @isladogs i'll take a further look at the link