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
                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
                    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
                    c.Font.Color = vbBlack
                End If
            End If
        End If
End Sub

Tuesday, June 04, 2013

Change Drive Label and Icon in Windows 7

I can use registry to do this. Here is an example.

Windows Registry Editor Version 5.00
@="Source Code"