SAS and VBA (1): Conditional formatting

“Traffic lighting” applies distinctive colors to any numeric variables to indicate the ranges, which is particularly important for Excel reporting. In SAS, it can be easily realized by a user defined format. For example, if I want to add yellow color attribute to all numeric variables, which are great than 60, I can create a color format and cast it toward the target Excel file created by ODS destination.

data class;
   set sashelp.class;
run;

* Create a user defined format
proc format; 
   value range 
      60 - high = 'yellow'
      other = 'white'; 
run;

* Apply the color format for all numeric variables
ods html file = "c:\tmp\label_excel.xls" style = minimal;
proc print data = class nobs;
   var _character_ ;
   var _numeric_ / style = [background = range.];
run;
ods html close;

Similarly, a VBA subroutine can do the global search based on the specified ranges with a looping structure around the numeric variables. The looking of the resulting Excel files by either SAS or VBA are essentially identical.

Sub global_label()
    Dim Cell As Object
    Dim myCell As Range
    Dim myRange As Range
   
'   Specify selection ranges
    If TypeName(Selection) <> "Range" Then Exit Sub
    If Selection.CountLarge = 1 Then
       Set myRange = ActiveSheet.UsedRange
    Else
       Set myRange = Application.Intersect(Selection, ActiveSheet.UsedRange)
    End If
    
'   Only search numeric cells 
    On Error Resume Next
    Set myRange = myRange.SpecialCells(xlConstants, xlNumbers)
    If myRange Is Nothing Then Exit Sub
    On Error GoTo 0
    
'   Aggregate cells
    For Each Cell In myRange
        If Cell.Value > 60 Then
            If myCell Is Nothing Then
                Set myCell = Cell
            Else
                Set myCell = Application.Union(myCell, Cell)
            End If
        End If
    Next Cell

'   Label qualified cells
    If myCell Is Nothing Then
        MsgBox "No matching cell is found"
    Else
        myCell.Select
        With Selection.Interior
            .Pattern = xlSolid
            .Color = 65535
        End With
    End If
End Sub