RGB values

[Joe Burns]

I have some questions about using color and conditional formatting in Excel.

1st, it looks like Excel 2007 may finally allow users to format cells by RGB
code but I haven’t yet figured out how exactly to make it work in the MS
online Excel 2007. I find MS elusive on the subject. Like “Diana†in the
COUNTIF thread, I do not want to go to the “extreme†of learning and using VB
if possible. Right now, I’m using Excel 2003.

I’m a holographer who wrote a small BASIC program in 1979 for following a
single ray through the hologram design process. It starts with a small number
of knowns about the finished hologram and ends with the correct optical
setups for recording that hologram.

I used CPM based Supercalc to enhance the program, moved it to DOS based
Lotus 123 and it now functions in Excel 2003. As part of the design process,
the current version allows a user to previsualize the colors of the final
hologram from each of three eye positions perpendicular to the top, middle,
and bottom of the hypothetical hologram. It allows the user to “see†the
colors the eye will see at each of those three positions at the top, middle,
and bottom of the hologram.

The spreadsheet generates the colors as wavelengths in nanometers and
converts the values to text in a vlookup table so the user has both numerical
wavelength values and textual color representations. See example below:

Eye > Red
Light red
Orange


Light red
Eye> Orange
Yellow


Orange
Yellow
Eye> Yellow/green


I have found a couple of very nice little freeware programs online which
will convert wavelength values to RGB values. I can make a lookup table for
the 430 visible integer wavelengths which will give their respective RGB
values.

MY PROBLEM: I can’t figure out how to use those RGB values to conditionally
format my cell background colors so I can have a “true†color representation
in each cell in addition to the text I now use. I originally requested this
as a feature from MS in the early 90s…

Here is the URL for one of the sites:
http://www.efg2.com/Lab/ScienceAndEngineering/Spectra.htm The aforementioned
zip file is at the bottom of the URL.

One of these days, I’ll bite the bullet and figure out how to convert the
xls to Mathcad ;-)

Thanks,
Joe Burns

 

[Bob Phillips]

Wouldn't you just set the RGB value, like this

With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(&H60, &H0, &H12)
.TintAndShade = 0
End With


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

Joe,

I think you will need a table of colour values, Violet works for me but not
for a computer.

So if we had a Colors table like so

390 Violet EE82EE
470 Navy Blue 000080
487 Light Blue ADD8E6

we could then use a worksheet change event to set the colour accordingly

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<== change to suit
Dim sColour As String

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
On Error Resume Next
sColour = Application.VLookup(.Value,
Application.Range("Colors"), 3, False)
On Error GoTo 0
If sColour <> "" Then
.Interior.Color = Application.Hex2Dec(sColour)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


The difficulty that I see is getting the colour codes.
--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)