Change Tab Color base on value of cell % Change

Printable View

04-12-2014, 02:01 AM
mrprofit

Change Tab Color base on value of cell % Change

I have code like this, but it does not change to the right color,

Code:

If Sheets("Sheet3").Range("S45").Value > "0.00" Then Sheets("Sheet3").Tab.ColorIndex = 5 '-----Change Tab Color (Blue) Sheets("Sheet1").Tab.ColorIndex = 5 '-----Change Tab Color (Blue) > 0.00% Sheets("Sheet2").Tab.ColorIndex = 5 '-----Change Tab Color (Blue) End If If Sheets("Sheet3").Range("S45").Value > "3.00%" Then Sheets("Sheet3").Tab.ColorIndex = 4 '-----Change Tab Color (Green) Sheets("Sheet1").Tab.ColorIndex = 4 '-----Change Tab Color (Green) > 3.00% Sheets("Sheet2").Tab.ColorIndex = 4 '-----Change Tab Color (Green) End If If Sheets("Sheet3").Range("S45").Value < "0.00" Then Sheets("Sheet3").Tab.ColorIndex = 46 '-----Change Tab Color (Orange) Sheets("Sheet1").Tab.ColorIndex = 46 '-----Change Tab Color (Orange) < 0.00% Sheets("Sheet2").Tab.ColorIndex = 46 '-----Change Tab Color (Orange) End If If Sheets("Sheet3").Range("S45").Value < "-3.00%" Then Sheets("Sheet3").Tab.ColorIndex = 3 '-----Change Tab Color (Red) Sheets("Sheet1").Tab.ColorIndex = 3 '-----Change Tab Color (Red) < -3.00% Sheets("Sheet2").Tab.ColorIndex = 3 '-----Change Tab Color (Red) End If End Sub
04-12-2014, 03:16 PM
Admin

Hi

try

Code:

Option Explicit Sub kTest() Dim tColor As Long tColor = Evaluate("=LOOKUP('Sheet3'!S45,{-9.99,3;-0.03,46;0,5;0.03,4})") Sheets("Sheet3").Tab.ColorIndex = tColor Sheets("Sheet1").Tab.ColorIndex = tColor Sheets("Sheet2").Tab.ColorIndex = tColor End Sub
04-12-2014, 05:17 PM
mrprofit

Works, Thank you

now i have another question, possible to use a cell R1 to replace the 0.03 in the code? R1 = 3%, so S45 < R1 then change Tabcolor to Red etc,
04-12-2014, 06:46 PM
Admin

Are you always compare S45 to R1 or different cells for different values ?
04-12-2014, 07:13 PM
mrprofit

I will compare it to R1 always, and possible to add another R2 =10%, then Change to Tabcolor to another color

Thank you for your help
04-14-2014, 11:14 PM
Admin

Hi

You mean like this ?

Code:

Option Explicit Sub kTest() Dim S45_Value As Double Dim R1_Value As Double S45_Value = Sheets("Sheet3").Range("S45").Value R1_Value = Sheets("Sheet3").Range("R1").Value Select Case True Case S45_Value > R1_Value If S45_Value > 0.03 Then Sheets("Sheet3").Tab.ColorIndex = 4 Sheets("Sheet2").Tab.ColorIndex = 4 Sheets("Sheet1").Tab.ColorIndex = 4 ElseIf S45_Value > 0 Then Sheets("Sheet3").Tab.ColorIndex = 5 Sheets("Sheet2").Tab.ColorIndex = 5 Sheets("Sheet1").Tab.ColorIndex = 5 End If Case S45_Value < R1_Value If S45_Value < 0 Then Sheets("Sheet3").Tab.ColorIndex = 46 Sheets("Sheet2").Tab.ColorIndex = 46 Sheets("Sheet1").Tab.ColorIndex = 46 ElseIf S45_Value < -0.03 Then Sheets("Sheet3").Tab.ColorIndex = 3 Sheets("Sheet2").Tab.ColorIndex = 3 Sheets("Sheet1").Tab.ColorIndex = 3 End If End Select End Sub
04-15-2014, 04:05 PM
mrprofit

instead of using all the sheets name, what code to just choose all sheets

Code:

Sheets("Sheet3").Tab.ColorIndex = 46 Sheets("Sheet2").Tab.ColorIndex = 46 Sheets("Sheet1").Tab.ColorIndex = 46

it this the code ?

Code:

worksheets.Tab.ColorIndex = 46
04-16-2014, 01:08 AM
Rick Rothstein

Quote:

Originally Posted by mrprofit

instead of using all the sheets name, what code to just choose all sheets

Code:

Sheets("Sheet3").Tab.ColorIndex = 46 Sheets("Sheet2").Tab.ColorIndex = 46 Sheets("Sheet1").Tab.ColorIndex = 46

it this the code ?

Code:

worksheets.Tab.ColorIndex = 46

Try it this way...

Code:

Dim WS As WorkSheet .... .... For Each WS in Worksheets WS.Tab.ColorIndex = 46 Next
04-16-2014, 10:38 AM
mrprofit

Thank you Rick