Macro - if red text then multiply by -1

[genxuser]

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

[mdmackillop]

Something like

Dim cel As Range
For Each cel In Selection
If cel.Font.ColorIndex = 3 Then
cel.Value = -1 * cel.Value
End If
Next

[Paul_Hossler]

One way


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


This leave the Font in Red, but you could easily change that, as well as anything else

Paul

[genxuser]

That worked. Thank you! Here's what the macro looks like

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

:)

[mdmackillop]

If you know the range, you can refer to it directly. no need to select it.
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