i want to call a VBA function(let it be func1(a)) in excel which returns value and simultaneously formats the cell in which the function is called. So suppose i call func1(2) from cell "A1" then i get 2 as output and background color of A1 becomes red. When func1(0.5) is called from "A1" then 0.5 is returned and background of "A1" becomes green.function func1(a)func1=a...some formatting code..End function

don't want to use conditional formatting

Didn't think this was possible, and tried various options on the following basic theme (this being the most complex), but Excel refuses to let me change format in mid Function. Don't think you can identify which cell your function is operating on. QuotePublic Function CellFormatter(ByVal ilNumber As Long) Dim lRow As Long Dim lCol As Long ilNumber = ilNumber + 5 lRow = ActiveCell.Row lCol = ActiveCell.Column If ilNumber > 10 Then Cells(lRow, lCol).Font.Bold = True Else Cells(lRow, lCol).Font.Bold = False End If CellFormatter = ilNumber End Function

cell formats can be only be changed from within Subs

This may get you started.Place this code in a standard module:=============Public r As RangePublic Function MyCalc(i, j)If r Is Nothing Then Set r = Application.ThisCellElse Set r = Union(r, Application.ThisCell)End IfMyCalc = i * jEnd Function============Place the code below in the "ThisWorkbook" modulePrivate Sub Workbook_SheetCalculate(ByVal Sh As Object) Dim c As Range If Not r Is Nothing Then For Each c In r.Cells If c.Value > 0.5 Then c.Interior.ColorIndex = 4 Else c.Interior.ColorIndex = 5 End If Next Set r = Nothing End IfEnd Sub