Summary tab colours

[mattless1]

Hello all,

i was kindly helped with a vba code today, which worked great, but i've come across another issue.

i have a summary sheet with a list of suppliers, which all have their own tabs. i wanted the suppliers in the summary sheet to match the colour of the sheet tabs. my problem is when the tabs are the normal colour the summary sheet supplier names cell go black and i need them to stay clear.

This is the code i'm using.

Sub ColorSupplier()
    Dim supplier As Range, ws As Worksheet
    For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
        For Each ws In ActiveWorkbook.Sheets
            If UCase(ws.Name) = UCase(supplier) Then
                supplier.Interior.Color = ws.Tab.Color
                Exit For
            End If
        Next ws
    Next supplier
End Sub

many thanks,
Mattless

 

[Fluff]

Try

Sub ColorSupplier()
    Dim supplier As Range, ws As Worksheet
    For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
        For Each ws In ActiveWorkbook.Sheets
            If UCase(ws.Name) = UCase(supplier) Then
               If ws.Tab.Color Then supplier.Interior.Color = ws.Tab.Color
               Exit For
            End If
        Next ws
    Next supplier
End Sub

 

[mattless1]

Hi Fluff, you seem to be my hero today lol!! that worked, but if i remove the colour it doesn't remove the colour on the summary sheet is that possible to

i'm using a Red, Green and no colour system. No colour is no invoices Green is all paid and Red is outstanding so, i change the colours to match the status of the invoice.

So i need to clear the colour when there no invoice.

sorry for being a pain.

Kind Regards,
Larry.

 

[Fluff]

There is no event that will recognise you changing the colour of a tab, so will need to run the macro manually.

 

[mattless1]

Hi Fluff,

That's what i'm doing i have an update button and the top of the sheet so if i change the colour i can update it, but if i remove the colour it doesn't change.

thanks for trying thou..

 

[Fluff]

Got yer, try

Sub ColorSupplier()
    Dim supplier As Range, ws As Worksheet
    For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
        For Each ws In ActiveWorkbook.Sheets
            If UCase(ws.Name) = UCase(supplier) Then
              supplier.Interior.Color = IIf(ws.Tab.Color, ws.Tab.Color, xlNone)
               Exit For
            End If
        Next ws
    Next supplier
End Sub

 

[mattless1]

Hi Fluff,

That is perfect works like a dream you deserve a medal helping us all out on here.

Again thanks very much.

Kind Regards,
Larry.

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rick Rothstein]

Got yer, try

Sub ColorSupplier()
    Dim supplier As Range, ws As Worksheet
    For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
        For Each ws In ActiveWorkbook.Sheets
            If UCase(ws.Name) = UCase(supplier) Then
              supplier.Interior.Color = IIf(ws.Tab.Color, ws.Tab.Color, xlNone)
               Exit For
            End If
        Next ws
    Next supplier
End Sub

I presume you used a double loop because the OP did, but this can be done with a single loop as well...

Sub ColorSupplier()
  Dim supplier As Range, ws As Worksheet
  For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
    With Sheets(supplier.Value).Tab
      If .Color Then supplier.Interior.Color = .Color
    End With
  Next supplier
End Sub

 

[Rick Rothstein]

I presume you used a double loop because the OP did, but this can be done with a single loop as well...

Sub ColorSupplier()
  Dim supplier As Range, ws As Worksheet
  For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
    With Sheets(supplier.Value).Tab
      If .Color Then supplier.Interior.Color = .Color
    End With
  Next supplier
End Sub

Sorry, the above doesn't work for a removed color, but this does...

Sub ColorSupplier()
  Dim supplier As Range, ws As Worksheet
  For Each supplier In Sheets("Summary").Range("A4", Sheets("Summary").Range("A" & Rows.Count).End(xlUp))
    With Sheets(supplier.Value).Tab
      supplier.Interior.Color = IIf(.Color, .Color, xlNone)
    End With
  Next supplier
End Sub