PDA

View Full Version : Changing a font style and color in VBA



AK_Beaver
05-02-2017, 02:27 PM
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
05-02-2017, 02:52 PM
You cannot use a UDF to change Font, Colours, etc. You could use conditional formatting.

SamT
05-02-2017, 04:03 PM
What Range is Data?

AK_Beaver
05-02-2017, 04:12 PM
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
05-03-2017, 09:03 PM
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
05-03-2017, 10:43 PM
I think

You should use "Conditional formatting"

AK_Beaver
05-03-2017, 11:05 PM
it has to be part of my code.

AK_Beaver
05-04-2017, 03:33 PM
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
05-04-2017, 04:20 PM
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

19086

AK_Beaver
05-04-2017, 10:15 PM
Oh man that's awesome! I finally figured out something similar but yours runs so much smoother. Thank you!