excelvbacolorstabscell

Write a loop that will check all tabs, a count of sheets, tab color changes based on 3 cell's values

I have a spreadsheet with multiple tabs. This is for pricing. The first tab can be changed in 4 rows to change the profit of each part number which lives on its OWN tab. I would like a code to loop all tabs, counting them and dumping that info on the first Tab sheet(i will need this to count color as well if possible). Then I would like the macro to check F5, G5, H5. Depending which one is negative would change the tab color. F5 is negative G5, H5 positive would turn tab Yellow G5 and H5 are negative and F5 is positive would turn the tab color Orange. Also if all 3 cells are all positive numbers i would like the Tab color Green IF all are negative numbers, then the tab changes to red.

I hope this makes sense, I am currently trying to get a multi-check "if statement" but having issues with: a lot. Thank you to anyone willing to help out.

If Range("G5") <= "-" Then
        ActiveWorkbook.Sheets("A5H3").Tab.Color = RGB(255, 165, 0)
        'do i need an else or End If?'
        If Range("F5") = "-" Then
        ActiveWorkbook.Sheets("A5H3").Tab.Color = RGB(255, 0, 0)
Solution

  • Something like:

    For Each mySheet In ActiveWorkbook.Sheets

    If mySheet.Range("G5") <= 0 Then
        mySheet.Tab.Color = RGB(255, 165, 0)
    ElseIf mySheet.Range("F5") <= 0 Then
        mySheet.Tab.Color = RGB(255, 0, 0)
    ElseIf mySheet.Range("E5") <= 0 Then
        mySheet.Tab.Color = RGB(0, 0, 255)
    End If

Next mySheet

    Should do what I think you want.