Want to show your appreciation? Please to my charity.

Monday, June 17, 2013

VBA Routine To Set Excel Cell Color By Value

I though some one must have already done this, but I cannot find the ready to use code. So I had to write one myself:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Column
    Dim Row
    Dim Red
    Dim Green
    Dim heat
    
    For Each c In Target
        Column = c.Column
        Row = c.Row
        
        Rem # Define the range of the cells to change color, keep this to minimal for better performance
        If Column > 1 And Column <= 12 And Row > 1 And Row < 40 Then
            If c.Value = "" Then
                c.Interior.ColorIndex = -4142
            Else
                Rem # Calculate the heat. You can times c.Value by a factor to control the range
                heat = c.Value - 255
                If (heat > 255) Then heat = 255
                If (heat < -255) Then heat = -255
                
                Rem # Set back color of the cell
                If (heat > 0) Then
                    Green = 256 - heat
                    Red = 255
                Else
                    Green = 255
                    Red = 255 + heat
                End If
                c.Interior.Color = RGB(Red, Green, 0)
                
                Rem # Set fore color of the cell
                If (heat > 100) Then
                    c.Font.Color = vbWhite
                Else
                    c.Font.Color = vbBlack
                End If
            End If
        End If
    Next
End Sub

2 comments:

Markus Schopenknecht said...

So this works for every cell that has a number in it? I am new to VBA, so excuse my question. I assume you only need this code with really big sheets? Since setting conditional formatting manually seems pretty fast and easy, at least according to this guide: http://www.excel-aid.com/excel-color-scales-highlighting-cells-with-color-scales.html . But dont give too much on my opinion, i am pretty unexperienced and just starting to get deeper into excel and stuff :)

Kenneth Xu said...

@Markus thanks for your comment. Conditional formatting works very well for background color but I didn't find a way to change text color. Black text on dark background looks pretty bad.

Post a Comment