PDA

View Full Version : Macro - if red text then multiply by -1



genxuser
09-03-2008, 09:00 AM
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
09-03-2008, 09:12 AM
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
09-03-2008, 09:57 AM
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
09-03-2008, 03:50 PM
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
09-03-2008, 11:56 PM
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