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:

Function AL(uH, Turns)
   If Turns > 0 Then
      AL = uH * 1000 / (Turns * Turns)
   Else
      AL = 0
   End If
   Select Case AL
          Case Is < 1
             AL = 0
             CI = 10
          Case Is < 10
             CI = 27
          Case Else
             CI = 28
   End Select
   With Selection.Interior
      .ColorIndex = CI
      .Pattern = xlSolid
   End With
   Selection.Interior.ColorIndex = CI
End Function

I'd found this snippet in the help file:

Using the Interior Object
Use the Interior property to return the Interior object. The following example sets the color for the interior of cell A1 to red.

Worksheets("Sheet1").Range("A1").Interior.ColorIndex = 3

 

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....

This article covers ways to change cells and cell properties in Excel, using Visual Basic for Applications, or VBA. VBA is the built-in macro programming langauge used by all Microsoft Office applications, and allows the programmer to change the properties according to the document model provided by the underlying application.
Sometimes it is useful to change the appearance of a cell based on it's contents. For example:

• Teacher's spreadsheets - low grades in red;
• Web site statistics - climbers in green, losers in red;
• Names in different colors, etc. etc.

In order to do this we need to isolate those cells that we wish to hilight, the criteria upon which we wish to make the decision, and the actual hilighting feature we wish to employ. In Excel, VBA provides a way to change many properties, including:

• Font (Text) Color;
• Font (Text) Size;
• Cell background color (shading);
• Cell borders, etc. etc.

For the purpose of this discussion, we shall assume that we have a spreadsheet, with a column titled 'Average', and that we want anything less than 50.0 to be hilighted by a red background. The sheet shall be called 'Totals'.

Referencing the Cells

Excel works with cells in ranges. A range can be one or more cells. These cells are contained in rows and columns in a sheet. Each sheet has a property, 'Cells', which allows us to reference such a range.
At it's smallest granularity, we might use this propery to reference a single cell:
Worksheets("Totals").Cells(1,1)
The above refers to a single cell, at Row 1, Column 1 - it is the equivalent to A1 on a spreadsheet. Row 1, Column 2, (1,2) would be cell B1. Row 2, Column 3 would be cell C2, and so on.
We can also refer to an entire column:
Worksheets("Totals").Columns(1)
The above refers to all of Column A. So, we have enough information to prepare the code that determines which column we are interested in; this was the first step we isolated in the Introduction.
We need to look at all the column headers, and note down which column has the word 'Average' in it. This can be done as follows:

Function FindColumn(szName) As Integer
 nFoundColumn = 0
 For nColumn = 1 To Worksheets("Totals").Columns.Count
  If StrComp(Worksheets("Totals").Cells(1, nColumn), szName) = 0 Then
    nFoundColumn = nColumn
  End If
 Next nColumn
 FindColumn = nFoundColumn
End Function

In this snippet, the For loop (code between the For... and Next... statements) allows us to move through the columns, nColumn by nColumn. We use StrComp to evaluate the contents against the szName parameter fed into the Function.
The function returns (using the line FindColumn = nFoundColumn) the index of the column containing the value szName.
Scrolling Through the Dataset

Once we have found the column, we can proceed to loop through the rows (starting at row 2, row 1 containing the heading), evaluating the contents of each cell. To look up the column, we use the following call to our FindColumn function:
nCol = FindColumn("Average")
If this call returns 0, we know that the column does not exist, and we need go no further. From our previous For code snippet above, we can construct a similar loop for the rows:

For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
  Rem Do Work Here
Next nRow

At each iteration of the loop, we need to evaluate the cell, and decide whether it is lower than the threshold (in this case, 50). However, given that the Count might include cells that are empty (rather than set to 0), we might not want to include them. This is especially true since Excel will run through all 65,000 rows, and evaluate each one. If we do not either:

• Tell Excel to stop at the last Row;
• Ignore empty cells.

There are therefore two possibilities - test for a known 'stop' value in a cell, or ignore empty cells. The advantage of having a stop value is that empty cells will be chosen, and hilighted, otherwise they need to be set to 0 in order to be chosen.
For the sake of simplicity, we will just ignore empty cells:

For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
 If Not IsEmpty(Worksheets("Totals").Cells(nRow, nCol)) Then
  If Worksheets("Totals").Cells(nRow, nCol) 50.0 Then
   Rem Hilight cell
  End If
 End If
Next nRow

The above uses the IsEmpty function to ascertain whether the cell contains any data. The double If (nested If) statement is used for clarity, a Boolean operator could have been used to combine the two statements.
Hilighting the Cell

Finally, we need to hilight the chosen cell:

Worksheets("Totals").Cells(nRow, nCol).Interior.ColorIndex = 3

The Interior property references the color and style of the shading. It uses an index, rather than a real color, and a collection of constants to set the shading style. These are out of scope for this article, but can be found in the Visual Basic for Excel online help files.
Summary

The above is just one way to achieve the goal, and is presented here for education, and not as the most elegant solution. In particular, the bounds checking for the data set, and hilighting code could be changed to allow for more flexibility / user friendliness. Fee free to post your contribution at the end of this page.
Budding Excel and Visual Basic or VBA programmers might find my article Starting with VBA for MS Excel of interest, as it covers extensions to the conditional summing functionality of Excel.
Non-programmers can refer to Conditional Sum Examples in Excel which covers conditional summing without the VBA.

Read more at Suite101: Changing Cell Color w/VBA in Excel: How to conditionally change cell properties based on contents. http://computerprogramming.suite101.com/article.cfm/changing_cell_color_w_vba_in_excel#ixzz0pQPFwaZG
​

​

I hope this helps....

B. Morse