-
Macro - if red text then multiply by -1
I've been extracting mutual fund data from the Fidelity Investments website for several years by downloading the webpage table to Excel. Recently Fidelity changed their display type for negative numbers to display in red, where as in the past the number would download as a negative.
I need help to create a macro in excel that will recognize the value in red, and then multiply it by -1. I've attached a file containing the output I need help with.
Thanks for looking at this.
Tim
Portland, OR
-
Something like
[VBA]
Dim cel As Range
For Each cel In Selection
If cel.Font.ColorIndex = 3 Then
cel.Value = -1 * cel.Value
End If
Next
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
One way
[VBA]
Sub FlipNumbers()
Dim rData As Range, rCell As Range
Set rData = Nothing
On Error Resume Next
Set rData = ActiveSheet.Cells(1, 1).CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If rData Is Nothing Then
Call MsgBox("No numbers on ActiveSheet", vbInformation + vbOKOnly, "FlipNumbers")
Exit Sub
End If
For Each rCell In rData.Cells
With rCell
If .Font.ColorIndex = 3 Then .Value = -1# * .Value
End With
Next
End Sub
[/VBA]
This leave the Font in Red, but you could easily change that, as well as anything else
Paul
-
That worked. Thank you! Here's what the macro looks like
[vba]Sub Macro1()
' Macro1 Macro
' Macro recorded 9/3/2008
'
'
Range("C6:G63").Select
Dim cel As Range
For Each cel In Selection
If cel.Font.ColorIndex = 3 Then
cel.Value = -1 * cel.Value
End If
Next
End Sub
[/vba]
Last edited by genxuser; 09-04-2008 at 07:10 AM.
-
If you know the range, you can refer to it directly. no need to select it.
[VBA]Dim cel As Range
For Each cel In Range("C6:G63")
If cel.Font.ColorIndex = 3 Then
cel.Value = -1 * cel.Value
End If
Next
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules