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 havent 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, Im using Excel 2003.

Im 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 cant 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/ScienceAndEn...ng/Spectra.htm The aforementioned
zip file is at the bottom of the URL.

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

Thanks,
Joe Burns

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)


"Joe Burns" wrote in message
...
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/ScienceAndEn...ng/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:
Your kind reply looks VBish to me. I'm not averse to trying it but I don't
know how.

A typical cell formula in my spreadsheet that gives a text color value
looks like this:
=VLOOKUP(B48,COLORS,2) where the 1st value is a cell reference to a value in
nanometers, the 2nd ref is the named array vlookup table, and the 3rd ref is
the 2nd column of that array.

The Colors named array vlookup table looks like this:
0 UV
390 VIOLET
455 DEEP BLUE
470 BLUE
487 LIGHTBLUE
494 BLUEGREEN
510 GREEN
565 YEL/GREEN
579 GRNYELLOW
580 YELLOW
590 ONGYELLOW
597 YELORANGE
605 ORANGE
614 REDORANGE
622 LIGHT RED
640 RED
670 DEEP RED
710 VY DP RED
770 INFRA RED
10000

I'm guessing I would do away with these formulas and this particular lookup
table and somehow substitute your VB? code and either a better lookup table
or a reference to Dan Bruton's "wavelength to RGB" code referenced in the URL
of my previous message.

Any guidance will be gratefully received.

Thanks,
Joe

"Bob Phillips" wrote:

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)

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)


"Joe Burns" wrote in message
...
Bob:
Your kind reply looks VBish to me. I'm not averse to trying it but I don't
know how.

A typical cell formula in my spreadsheet that gives a text color value
looks like this:
=VLOOKUP(B48,COLORS,2) where the 1st value is a cell reference to a value
in
nanometers, the 2nd ref is the named array vlookup table, and the 3rd ref
is
the 2nd column of that array.

The Colors named array vlookup table looks like this:
0 UV
390 VIOLET
455 DEEP BLUE
470 BLUE
487 LIGHTBLUE
494 BLUEGREEN
510 GREEN
565 YEL/GREEN
579 GRNYELLOW
580 YELLOW
590 ONGYELLOW
597 YELORANGE
605 ORANGE
614 REDORANGE
622 LIGHT RED
640 RED
670 DEEP RED
710 VY DP RED
770 INFRA RED
10000

I'm guessing I would do away with these formulas and this particular
lookup
table and somehow substitute your VB? code and either a better lookup
table
or a reference to Dan Bruton's "wavelength to RGB" code referenced in the
URL
of my previous message.

Any guidance will be gratefully received.

Thanks,
Joe

"Bob Phillips" wrote:

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)