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.