AAAAA: EXCEL Macros: Macro AA

Macro AA_FreqValColor

The relative amino acid frequency tables generated by AA_Frequency give a good indication of the sequence conservation and amino acid preference at each position. However, global pattern are hard to see in unstructured tables. Coloring the table according to the values in the individual cells and hiding the "0" values helps to perceive global pattern.

Usage:

generate an amino acid frequency table by applying the macro "AA-Frequency" to a sequence alignment
select the range of cells in the frequency table to be colored
run macro AA_FreqValColor



Sub AA_FreqValColor()

'Colors the cells of a table according to the values contained in the cells
'Cells containing an exact zero are colored white and lettered white - the value is hidden
'Cells containing a value between zero and one are colored very light blue. The value is shown as "0" if  it is <0.5 (rounded)
'1-10: light blue, 10-25: medium blue, 25-90: dark blue, >90: very dark blue

    If Selection.Columns.Count = 0 Then      'Error, nothing selected
        MsgBox Prompt:="No cells selected, please select the cells you wish to color"
        Exit Sub
    End If

    Selection.Borders(xlLeft).LineStyle = xlNone
    Selection.Borders(xlRight).LineStyle = xlNone
    Selection.Borders(xlTop).LineStyle = xlNone
    Selection.Borders(xlBottom).LineStyle = xlNone
    Selection.BorderAround Weight:=xlThick
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.Name = "Geneva"
    Selection.Font.FontStyle = "Regular"
    Selection.Font.Size = 9
    Selection.Font.ColorIndex = 1
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.NumberFormat = "0"
    Selection.ColumnWidth = 4
 
'get extent of current selection
i1 = Selection.Row
i2 = i1 + Selection.Rows.Count - 1
j1 = Selection.Column
j2 = j1 + Selection.Columns.Count - 1

For j = j1 To j2 Step 1 'row i1-i2, column j1-j2'
    For i = i1 To i2 Step 1
        k = 1 'black
        l = 2 'white
        If (IsNumeric(Cells(i, j)) And Not (IsEmpty(Cells(i, j)))) Then
            If Cells(i, j).Value = 0 Then
                k = 2 'white
                l = 2 'white
                ElseIf Cells(i, j).Value < 1 Then
                    k = 29 'very light blue
                    l = 1
                    ElseIf Cells(i, j).Value < 10 Then
                        k = 28 'light blue
                        l = 1
                        ElseIf Cells(i, j).Value < 25 Then
                            k = 27 'medium blue
                            l = 2
                            ElseIf Cells(i, j).Value < 90 Then
                                k = 26 'dark blue
                                l = 2 'white
                                Else
                                    k = 25 'very dark blue
                                    l = 2
            End If
                Cells(i, j).Select
                With Selection.Interior
                    .ColorIndex = k
                    .Pattern = xlSolid
                End With
                Selection.Font.ColorIndex = l
        Else
            Cells(i, j).Select
            With Selection.Interior
                .ColorIndex = 1
                .Pattern = xlSolid
            End With
            Selection.Font.ColorIndex = 1
        End If
    Next i

Next j

End Sub

Last Modified by A.Honegger Wednesday, January 26, 2005