Multiplying Cells Based on Location And Font Color

Question

This should be a fairly easy question for you guys, but I can't seem to find a solution online. I have three columns in my dataset where negative values are displayed in red instead of with a preceding '-' sign. I need to multiply these values by -1 if they are in in columns E, I and M AND their font color is -16776961. I've seen a few pieces of code that get close But I don't know how to modify them to do what is required:

If Range("E5:E100").Value = 1 Then Range("").Value = Range("B2").Value * 10

I was also thinking I could do something like this:

For Each c In r
    With c
        If Not IsError(.Value) Then
            Select Case .Color
                 Case -16776961
                     .Value = .Value * -1
            End Select
        End If
    End With
Next c

But I'm just not sure what the exact syntax is.

Thanks in advance to anyone who gives this a shot.

Another attempt:

Range("U1").Select
ActiveCell.FormulaR1C1 = "-1"

Rows("4:4").Select
Selection.AutoFilter

Range("U1").Select
Selection.Copy

ActiveSheet.Range("$A$4:$X$43").AutoFilter Field:=5, _
                                           Criteria1:=RGB(232, 88, 88), _
                                           Operator:=xlFilterFontColor
Range("E11:E52").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
                       Operation:=xlMultiply, _
                       SkipBlanks:=False, _
                       Transpose:=False

Do you know what the code you found do? The first one doesn't even make sense and the second one should work if you set r to columns E, I and M... — findwindow, Sep 24 '15 at 21:44
Use your macro recorder to filter by color, then you could use this to multiply the filtered range http://stackoverflow.com/questions/18990541/multiply-entire-range-by-value — Davesexcel, Sep 24 '15 at 21:59
I tried using the macro recorded with the filter command, but I can't figure out how to multiply the filtered values instead of the range they are in. I am using paste special to multiply the filtered range by a cell with a -1 inside. When I clear the filter, the paste special applies to a portion of the column that is the same length as the filtered range following the first red number. (Ex. Once filtered, the column is reduced from 10 values to 5 values long. Those values are selected and modified. The filter is cleared and now 5 values green or red are modified following the first red value — sevans, Sep 25 '15 at 13:35
So I think I've figured out the above problem (sort of). I used the 'go to special' command of selecting only visible cells to avoid selecting a range. I then paste special and multiply by -1, but I am always met with a paste special method of range class error when running through vba. — sevans, Sep 25 '15 at 15:05
This is the best I can do as far as posting code:Range("U1").Select ActiveCell.FormulaR1C1 = "-1" Rows("4:4").Select Selection.AutoFilter Range("U1").Select Selection.Copy ActiveSheet.Range("$A$4:$X$43").AutoFilter Field:=5, Criteria1:=RGB(232, 88 _ , 88), Operator:=xlFilterFontColor Range("E11:E52").Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False — sevans, Sep 25 '15 at 15:06

paul bica · Answer 1 · 2015-09-26T18:31:53.233

If NumberFormat is not [Blue]#,##0.00_);[Red](#,##0.00);0.00, this code toggles red numbers

Option Explicit

Public Sub convertNegatives()
    Const FIRST_ROW     As Long = 4
    Const CONVRT_COL    As String = "E I M" 'Columns to process

    Dim ws As Worksheet, ur As Range, cc As Variant, c As Variant, clr As Long

    clr = RGB(232, 88, 88)
    Set ws = ActiveSheet
    cc = Split(CONVRT_COL)

    With ws.UsedRange
       Set ur = .Range(ws.Cells(FIRST_ROW - 1, 1), .Cells(.Rows.Count - 1, .Columns.Count))
    End With

    Application.ScreenUpdating = False
    For Each c In cc
       convertColorFilter ur.Columns(c), clr    'Option 1
       'convertColorLoop ur.Columns(c), clr     'Option 2
    Next
    Application.ScreenUpdating = True
End Sub

Option 1 (AutoFilter)

Private Sub convertColorFilter(ByRef col As Range, ByVal clr As Long)
    Dim cel As Range, vr As Range

    col.Parent.AutoFilterMode = False
    col.AutoFilter Field:=1, Criteria1:=clr, Operator:=xlFilterFontColor

    Set vr = col.Cells.SpecialCells(xlCellTypeVisible)

    If vr.Count > 0 Then
        For Each cel In vr
            If cel.Font.Color = clr Then cel.Value2 = cel.Value2 * -1
        Next
    End If: col.Parent.AutoFilterMode = False
End Sub

Option 2 (For loop)

Private Sub convertColorLoop(ByRef col As Range, ByVal clr As Long)
    Dim cel As Range
    If col.Columns.Count = 1 Then
        For Each cel In col.Cells
            If cel.Font.Color = clr Then cel.Value2 = cel.Value2 * -1
        Next
    End If
End Sub

Unfortunately, the numbers I'm dealing with aren't formatted like that. They are all positive numbers but some have a green font color and some have a red font color. — sevans, Sep 25 '15 at 13:27

Multiplying Cells Based on Location And Font Color

1 Answers1