Format cell color based on multiple variables
 

I am creating a spreadsheet that imports data from another sheet.
group of cells are to be assigned a color based on thier imported data
however, the color assignments need to be able to be defined and change
from a "key" which I want to locate on the page itself. see attached fo
example.

Any thoughts on a good way to go about doing this?
I have been playing with formulas all day. I can use conditiona
formatting but that only defines the color based on the number in th
cell and does not allow me to change the numerical color range from th
page itself.

Thoughts

+-------------------------------------------------------------------
|Filename: excel.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4291
+-------------------------------------------------------------------

--
chuzi
-----------------------------------------------------------------------
chuzie's Profile: http://www.excelforum.com/member.php...fo&userid=3105
View this thread: http://www.excelforum.com/showthread.php?threadid=50725

Format cell color based on multiple variables
 

Hi,
Here is simple possible solution. A named range ("Colours") contains
the upper limits of each band in your data AND is colour-coded i.e. filled
with required colour.

Macro then loops through cells and changes colours according to value in
"Colours" table.

Colours ==named range sorted in descending order
0.73
0.54
0.43
0.00

Sub assignColour()
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Range("d1:d" & lastrow) ' <=== imported data
For Each cell In rng
For Each c In Range("colours") ' <=== Named range
If cell = c Then
cell.Interior.ColorIndex = c.Interior.ColorIndex
Exit For
End If
Next c
Next cell
End Sub

HTH

"chuzie" wrote:


I am creating a spreadsheet that imports data from another sheet. A
group of cells are to be assigned a color based on thier imported data,
however, the color assignments need to be able to be defined and changed
from a "key" which I want to locate on the page itself. see attached for
example.

Any thoughts on a good way to go about doing this?
I have been playing with formulas all day. I can use conditional
formatting but that only defines the color based on the number in the
cell and does not allow me to change the numerical color range from the
page itself.

Thoughts?


+-------------------------------------------------------------------+
|Filename: excel.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4291 |
+-------------------------------------------------------------------+

--
chuzie
------------------------------------------------------------------------
chuzie's Profile: http://www.excelforum.com/member.php...o&userid=31053
View this thread: http://www.excelforum.com/showthread...hreadid=507255

Format cell color based on multiple variables
 

That makes perfect sense but I am not familiar with the code

lastrow = Cells(Rows.Count, "D").End(xlUp).Row


--
chuzie
------------------------------------------------------------------------
chuzie's Profile: http://www.excelforum.com/member.php...o&userid=31053
View this thread: http://www.excelforum.com/showthread...hreadid=507255

Format cell color based on multiple variables
 

It finds the last (non-blank) row in the column (D in the example) so you can
define the range of data to process. If your range is fixed then there is no
need to use this but ranges usually vary in size (number of rows) so this
construct is frequently used.


HTH

"chuzie" wrote:


That makes perfect sense but I am not familiar with the code

lastrow = Cells(Rows.Count, "D").End(xlUp).Row


--
chuzie
------------------------------------------------------------------------
chuzie's Profile: http://www.excelforum.com/member.php...o&userid=31053
View this thread: http://www.excelforum.com/showthread...hreadid=507255