Changing a font style and color in VBA

[AK_Beaver]

I'm trying to write a function that takes two numbers as input and returns the product of the two. If the product is negative I want it to be colored red and italicized, If positive it should be colored blue. I've started a code but I keep getting the output #VALUE!. Is there a way to do it without calling on the specific worksheet in the code?

Function Multiplier(dNumber1 As Double, dNumber2 As Double) As Double

Dim dProduct As Double
Dim Data As Range, Cell As Range


dProduct = dNumber1 * dNumber2
Multiplier = dProduct


For Each Cell In Data
    If Cell.Value < 0 Then
        Cell.Font.Italic = True
        Cell.Font.Color = 3
    
    Else: Cell.Font.Color = 6
End If
Next


End Function

[mdmackillop]

You cannot use a UDF to change Font, Colours, etc. You could use conditional formatting.

[SamT]

What Range is Data?

[AK_Beaver]

I guess what I'm trying to do is that have the function wrong on a cell by cell basis. So I can pick a random cell put the function name in and have the output be italicized or not and have it be the desired color. Is that possible.

[AK_Beaver]

I'm trying to write a function that takes two numbers as input and returns the product of the two. If the product is negative, I want to color it red and italicize it and if it is positive, it should be blue. Two problems I'm running into. The code wont italicized the negative numbers and you can't just drag the code down. You have to manually type the function into each cell. It works otherwise...multiplies the two numbers and changes color. Any suggestions? Thanks! option explicit option base 1 Function Multiplier(dNumber1 As Double, dNumber2 As Double) As Double Dim dProduct As Double dProduct = dNumber1 * dNumber2 If dProduct < 0 Then ActiveCell.Font.Italic = True ActiveCell.Font.Color = RGB(255, 0, 0) Else: ActiveCell.Font.Color = RGB(0, 0, 255) End If Multiplier = dProduct End Function

[mana]

I think

You should use "Conditional formatting"

[AK_Beaver]

it has to be part of my code.

[AK_Beaver]

How would I go about using conditional formatting to get my desired end result...Negative values are colored red and italicized and positive numbers colored blue. I'm super confused.

[Paul_Hossler]

If you want to avoid using CF, then maybe put this in the WS code module

Option Explicit
 
Private Sub Worksheet_Calculate()
    Dim c As Range
    
    On Error Resume Next
    
    For Each c In Me.UsedRange.SpecialCells(xlCellTypeFormulas).Cells
        If c.Formula Like "=Multiplier*" Then
            If c.Value < 0 Then
                c.Font.Italic = True
                c.Font.Color = RGB(255, 0, 0)
            Else
                c.Font.Italic = False
                c.Font.Color = RGB(0, 0, 255)
            End If
        End If
    Next
End Sub

and this in a standard module

Option Explicit
Option Base 1
 
Function Multiplier(dNumber1 As Double, dNumber2 As Double) As Double
     
    Dim dProduct As Double
     
    dProduct = dNumber1 * dNumber2
     
    Multiplier = dProduct
     
End Function

To get the formula to flow, you have to use relative addresses

Capture.JPG

[AK_Beaver]

Oh man that's awesome! I finally figured out something similar but yours runs so much smoother. Thank you!