Return to Archive

SAS Tip of the Month
July 2004

The three big SAS conferences are over for another year and it is now back to work and use what has been learnt. If you were not there SAS will start making available the conference proceedings shortly. Your local SAS User group will also be probably presenting highlights of the conferences so I urge you to support your local group.

I receive a number of Excel spreadsheets each month from organizations with data to review. Some of these worksheets have highlighted cells. But how do you find the cells highlighted in a worksheet that has approximately 5000 rows? One way is to determine the highlight color of the cell using the following VBA code:

   Function showColorCode(rcell)
      showColorCode = rcell.Interior.ColorIndex
   End Function

To enter the VBA code into Excel select

   Tools > Macro > Visual Basic Editor


   Insert > Module

Then paste the code above and then press

   File > Close and Return to Microsoft Excel

The function is now ready for use within Excel and can be accessed from the User Defined function list.

The codes returned from the function will show the background color of the cell. Microsoft unfortunately did not keep codes consistent across different releases of Excel. The following list is a general guide that is useful when referring to color codes for Excel 97:

  • -4142 = No Color
  • 1 = Black
  • 2 = White
  • 3 = Red
  • 5 = Blue
  • 6 = Yellow
  • 10 = Green
Updated July 9, 2004