VBA Macro to change the value of the cell based on the text box font color. ( -ve for Red font)

[bpediredla]

Hello,

I am a total VBA novice and I really need some help with this problem.

I have 10 excel files that contain 5 worksheets in each of them. The values of 3rd worksheet (of all excel files) of Cell C24 change regularly to Red or Green font values.


I'd like to change the red color values of C24 to negative values and rest of them as positive values. (color coding logic is consistent where negative = red and positive = green)

I do not have any existing code to work from.


Any suggestions is greatly appreciated?

 

[DanteAmor]

Use the following macro.
Change "C:\trabajo\books" to the folder where your files are

Sub Change_The_Value()
'   Macro to change the value of the cell based on the text box font color
    '
    Application.ScreenUpdating = False
    Application.StatusBar = False
    ruta = "C:\trabajo\books\"
    arch = Dir(ruta & "*.xls*")
    Do While arch <> ""
        Application.StatusBar = "File Process : " & arch
        Set l2 = Workbooks.Open(ruta & arch)
        If l2.Sheets.Count >= 3 Then
            Set celda = l2.Sheets(3).Range("C24")
            If celda.Value <> "" Then
                
                If celda.Font.ColorIndex = 3 Then  'rojo
                    If celda.Value > 0 Then
                        celda.Value = celda.Value * -1
                    End If
                ElseIf celda.Font.ColorIndex = 4 Then    'verde
                    If celda.Value < 0 Then
                        celda.Value = celda.Value * -1
                    End If
                End If
            End If
        End If
        l2.Save
        l2.Close False
        arch = Dir()
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "End"
End Sub