colouring cells with if..then..else if value = empty or 0 1

[andrew299]

What I am trying to do is colour code cells according to the value contained within. I understand this is possible to do this with conditional formatting but this can only have three ranges per cell. I have ten.
The data is placed in another part of the sheet and in the section I want the colour 'map' the formula =a1 etc is put in. I can colour the cells in my ranges using if statements but the problem comes when the value is zero.
To me a zero is important and my highest value and I have coloured it Red but Excel recognises a cell with 0 in it as empty and colours it black (my lowest colour).
This occurs due to the =a1 statements in the formula bar. I want the macro as robust as possible so how do I get excel to recognise that empty=empty and zero is something different.

Hope that makes sense

Thanks
Andrew299


It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[SkipVought]

Andrew299,

Select the area to shade

    Dim c As Range
    For Each c In Selection
      With c
        If Not IsEmpty(.Value) Then
          .Interior.ColorIndex = .Value
        End If
      End With
    Next

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com