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.
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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.