Using VBA to change sheet tab color to a particular color if found in range.

[dugweje]

Hello. I have sheets that I download for my job that have a shade of red in a cell to indicate that it is over the limit. The range is A:1 to A:3000. I was trying to create a code that if any cell( even if just one) in that range is the shade of red the sheet tab would also turn into that shade of red. Thank you any help is appreciated.

 

[BSALV]

have a shade of red in a cell

a shade of red, does that mean a "fixed" red color, normally done in the 3 variabels of RGB, like RGB(250,10,10) = 1 value
or is it not that fixed.
in human talk, is it "that color red", without variation ?
In excel, you can have hundreds of colors "red" !

 

[bebo021999]

Option Explicit
Sub test()
Dim Lr&, cell As Range
Lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In Range("A1:A" & Lr)
        ' If cell color is red with RGB code = 3, use this
        If cell.Interior.ColorIndex = 3 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex
        ' If any color is found then tab color is set with same color, use this
        'If cell.Interior.ColorIndex >0 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex
    Next
End Sub

Use: for red =3
If cell.Interior.ColorIndex = 3 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex
Or any color found:
If cell.Interior.ColorIndex >0 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex

 

[dugweje]

Option Explicit
Sub test()
Dim Lr&, cell As Range
Lr = Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In Range("A1:A" & Lr)
        ' If cell color is red with RGB code = 3, use this
        If cell.Interior.ColorIndex = 3 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex
        ' If any color is found then tab color is set with same color, use this
        'If cell.Interior.ColorIndex >0 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex
    Next
End Sub

Use: for red =3
If cell.Interior.ColorIndex = 3 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex
Or any color found:
If cell.Interior.ColorIndex >0 Then ActiveSheet.Tab.ColorIndex = cell.Interior.ColorIndex

This worked perfectly thank you so much