Dynamic cell shading macro

[lewis1]

Hi,

I have a cell which has the intraday % return of a stock price.

If possible, as the cell changed from 0.0% to eg. +5.0%, I would like the cell to change from a light green to bright green.

In the same way, as the cell goes from 0.0% to eg: -5.0% it would go from a light red to bright red.

Is this something that can be done easily? Any assistance would be great.

Thanks,
Lewis

 

[Sal Paradise]

This can be done with conditional formatting to an extent.

Format -> Conditional Formatting

Depending on your version, you would be limited to 4 different colors (prior to 2007).

Am I misunderstanding the question?

 

[Misca]

If you're using Excel 2007 or 2010 it's easy to do that with Conditional Formatting: http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html

If you're using older versions you need a macro to do that if you want to use several shades (and even then the shades will be quite ugly when compared to the first solution):

Sub Colors()

Dim Rng As Range
Dim c As Range
Dim i As Long

Set Rng = Range("A1:A5, E1:E15") 'Set the range to color

For Each c In Rng
    With c
        Select Case .Value
            Case Is < -0.04
                i = 5
            Case -0.04 To -0.02
                i = 6
            Case -0.02 To -0.01
                i = 7
            Case 0.01 To 0.02
                i = 8
            Case 0.02 To 0.04
                i = 9
            Case Is > 0.04
                i = 10
            Case Else   'Just in case:
                i = .Interior.ColorIndex
        End Select
    
        .Interior.ColorIndex = i
    
    End With
        
Next c

End Sub

To get the macro work you need to figure out a way to fire it (use an event macro or something) and choose the proper i values. If you want to, you can also add more values to the Select Case part to get more shades / colors.

 

[lewis1]

Hi,

Conditional formatting would be ideal, yet on my version of excel I can only have 3 conditions... which isn't really enough.

I'll give the macro above a try and see how we go.

Thanks for your help!

Lewis.

 

[crspycrtr]

I have a similar issue, but mine has to do with identifying dates concatenated from cells.

I have a range of cells that make up a linear calendar. Month Name in Column A, month number in column B and the days in row C9:AG9

I need to dynamically identify when an cell array is equal to a weekend day, i.e. cell C10 = 1/1/2012 which is a Sunday, I10 is a Saturday etc.

Also I would need to know when the last day column 29-31 is not in that month, i.e. AF12:AG12 and color that differently.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 81px"><COL style="WIDTH: 30px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD><TD>AB</TD><TD>AC</TD><TD>AD</TD><TD>AE</TD><TD>AF</TD><TD>AG</TD></TR><TR style="HEIGHT: 33px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">2012</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: center">31</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Jan</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center" rowSpan=2>Feb</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #000000"> </TD><TD style="BACKGROUND-COLOR: #000000"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">2</TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #000000"> </TD><TD style="BACKGROUND-COLOR: #000000"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4