Format cell color based on multiple variables

C

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

Toppers

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
 
C

chuzie

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

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

Toppers

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top