Format cell color based on multiple variables

[chuzie]

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
+-------------------------------------------------------------------

 

[Guest]

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]

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

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

 

[Guest]

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