If statement based on text color

[thardin]

At Work, I have a report that I process on a daily basis similar to this layout.


I want to create a macro that will automatically perform a calculation in Column U(as shown) for the red items.
However, the amount of rows change daily and sometimes there are also black rows and sometimes not. And sometimes I have to do this on multiple tabs(up to 3 tabs).
is an If statement possible for text color. Such as if(s4=red,s4-r4, " ").

How would I write VBA to:
- calculate S4-R4 and down for ONLY the red items,(in the format of a percent up to 3 decimal places)
- on all tabs of the workbook,
- and in the shortcut Ctrl + Shift+ J

And also within the same macro, I want to perform an "inspection" to check if all the numbers I just calculated in Column U are NOT in between -.05 and 0 percent. And if so, a pop-up box appears and reads "no violations found."

Thanks.

 

[tonyyy]

You might consider the following...

Sub RedReport()
Dim ws As Worksheet
Dim r As Range, c As Range
Dim txt As String
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Sheets
    ws.Range("A3:S8" & ws.Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter _
        Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
    Set r = ws.AutoFilter.Range.Offset(0, 2).SpecialCells(xlCellTypeVisible)
    Set r = Intersect(ws.Columns("U"), r).Resize(r.Rows.Count - 1).Offset(1, 0)
    With r
        .FormulaR1C1 = "=RC[-2]-RC[-3]"
        .NumberFormat = "0.000%"
        .Font.Color = -16776961
        .Font.TintAndShade = 0
    End With
    For Each c In r
        If c.Value > -0.005 And c.Value < 0 Then
            If txt = "" Then
                txt = vbCrLf & ws.Name & " " & c.Address(0, 0)
            Else
                txt = txt & vbCrLf & ws.Name & " " & c.Address(0, 0)
            End If
        End If
    Next c
    If ws.AutoFilterMode Then
       ws.AutoFilterMode = False
    End If
Next ws
Application.ScreenUpdating = True
If txt <> "" Then
    MsgBox "violations found in:" & vbCrLf & txt
Else
    MsgBox "no violations found"
End If
End Sub

Cheers,

Tony