MS Excel: Changing background color of a cell in a VB macro?

Discussion in 'Programmer's Corner' started by SgtWookie, May 30, 2010.

  1. SgtWookie

    Thread Starter Expert

    Jul 17, 2007
    22,182
    1,728
    I'd never tried this before, but decided I'd like to change the background or text color in the cell via a function call, depending upon the value of one of the arguments supplied to the function AL(uH,Turns).

    This should be really simple; I just haven't tried to do this before and I am not certain how the cell range (where the function was called from) could be passed to the function, without having to actually enter it into the formula.

    This is what I've been fiddling with:
    Code ( (Unknown Language)):
    1. Function AL(uH, Turns)
    2.    If Turns > 0 Then
    3.       AL = uH * 1000 / (Turns * Turns)
    4.    Else
    5.       AL = 0
    6.    End If
    7.    Select Case AL
    8.           Case Is < 1
    9.              AL = 0
    10.              CI = 10
    11.           Case Is < 10
    12.              CI = 27
    13.           Case Else
    14.              CI = 28
    15.    End Select
    16.    With Selection.Interior
    17.       .ColorIndex = CI
    18.       .Pattern = xlSolid
    19.    End With
    20.    Selection.Interior.ColorIndex = CI
    21. End Function
    22.  
    I'd found this snippet in the help file:
    Code ( (Unknown Language)):
    1. Using the Interior Object
    2. Use the Interior property to return the Interior object. The following example sets the color for the interior of cell A1 to red.
    3.  
    4. Worksheets("Sheet1").Range("A1").Interior.ColorIndex = 3
    5.  
     
  2. BMorse

    Senior Member

    Sep 26, 2009
    2,675
    234
    here is a snippet from an article I have read online about the same exact thing you are looking for, I actually just used the same functions in an Excel Spread sheet I was doing for a customer of mine....

    I hope this helps....

    B. Morse
     
Loading...