| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro - color tab
I have the following macro:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro - color tab
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone ElseIf Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) ElseIf Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... I have the following macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro - color tab
Bob,
Thanks for the help. It works great as long as there is no formula in cell U246. I have this formula in the cell: (R246/T246)/24. If the result is <120, the tab should turn red. Is there a way for xl to look only at the result in the cell and not the formula? Thanks again, rob "Bob Phillips" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone ElseIf Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) ElseIf Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... I have the following macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro - color tab
It works for me with that formula in the cell. It doesn't matter as it
checks the cell value. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... Bob, Thanks for the help. It works great as long as there is no formula in cell U246. I have this formula in the cell: (R246/T246)/24. If the result is <120, the tab should turn red. Is there a way for xl to look only at the result in the cell and not the formula? Thanks again, rob "Bob Phillips" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone ElseIf Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) ElseIf Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... I have the following macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro - color tab
Am I missing something? I copied what you sent and I get the same result as
before. The only way it works is if I clear the formula and put in the result. I even tested it in a new, blank workbook and got the same result. I have even inserted this statement, Sh.Range("U246").Formula = "=(R246/T246)/24", before this statement, If Not Application.Intersect(Target, Range("U246")) Is Nothing Then. It works just like I want it to, but whenever data is entered I get a run time 13 error - type mismatch. Any suggestions on the run time error or eliminating the formula portion? Thanks for all your help, rob "Bob Phillips" wrote: It works for me with that formula in the cell. It doesn't matter as it checks the cell value. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... Bob, Thanks for the help. It works great as long as there is no formula in cell U246. I have this formula in the cell: (R246/T246)/24. If the result is <120, the tab should turn red. Is there a way for xl to look only at the result in the cell and not the formula? Thanks again, rob "Bob Phillips" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone ElseIf Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) ElseIf Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... I have the following macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro - color tab
Rob,
Can you post me your workbook and I will take a look? Post to bob dot ngs at googlemail dot com do the obvious with dot and at. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... Am I missing something? I copied what you sent and I get the same result as before. The only way it works is if I clear the formula and put in the result. I even tested it in a new, blank workbook and got the same result. I have even inserted this statement, Sh.Range("U246").Formula = "=(R246/T246)/24", before this statement, If Not Application.Intersect(Target, Range("U246")) Is Nothing Then. It works just like I want it to, but whenever data is entered I get a run time 13 error - type mismatch. Any suggestions on the run time error or eliminating the formula portion? Thanks for all your help, rob "Bob Phillips" wrote: It works for me with that formula in the cell. It doesn't matter as it checks the cell value. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... Bob, Thanks for the help. It works great as long as there is no formula in cell U246. I have this formula in the cell: (R246/T246)/24. If the result is <120, the tab should turn red. Is there a way for xl to look only at the result in the cell and not the formula? Thanks again, rob "Bob Phillips" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone ElseIf Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) ElseIf Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... I have the following macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro - color tab
This will work but ONLY if a change is made to the active sheet. Is that
what you want?? Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 'If Target.Address < "$E$2" Then Exit Sub If Sh.Range("e2") = 120 Then 'If Target = 120 Then Sh.Tab.ColorIndex = 46 Else Sh.Tab.ColorIndex = xlNone End If End Sub -- Don Guillett SalesAid Software "rob" wrote in message ... Am I missing something? I copied what you sent and I get the same result as before. The only way it works is if I clear the formula and put in the result. I even tested it in a new, blank workbook and got the same result. I have even inserted this statement, Sh.Range("U246").Formula = "=(R246/T246)/24", before this statement, If Not Application.Intersect(Target, Range("U246")) Is Nothing Then. It works just like I want it to, but whenever data is entered I get a run time 13 error - type mismatch. Any suggestions on the run time error or eliminating the formula portion? Thanks for all your help, rob "Bob Phillips" wrote: It works for me with that formula in the cell. It doesn't matter as it checks the cell value. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... Bob, Thanks for the help. It works great as long as there is no formula in cell U246. I have this formula in the cell: (R246/T246)/24. If the result is <120, the tab should turn red. Is there a way for xl to look only at the result in the cell and not the formula? Thanks again, rob "Bob Phillips" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone ElseIf Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) ElseIf Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "rob" wrote in message ... I have the following macro: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Application.Intersect(Target, Range("U246")) Is Nothing Then If Target.Value = IsError(xlErrDiv0) Then Sh.Tab.ColorIndex = xlColorIndexNone Else If Target.Value < 120 Then Sh.Tab.Color = RGB(255, 0, 0) Else If Target.Value = 120 Then Sh.Tab.ColorIndex = xlColorIndexNone End If End If End If End If End Sub It is suppose to change the tab color to red when cell U246 is below 120. Instead it changes color when a value below 120 is entered into any cell. I have this code in a workbook module, so it works on all worksheets in the workbook. There is a formula in cell U246. When the value of this formula is <120, the tab should turn red. How do I get it to look at only cell U246 and not all cells to decide when to change color? Thanks in advance for any help, rob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| error when running cut & paste macro | Excel Worksheet Functions | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
Linear Mode
