Changing a cell to the same tab index color as its value
 

I changed some of my other module code to pick up a number on this Worksheet
to use for the tab color index. These references are in cells O6 through O30.
It would be nice so that the €œO€� cell where number is stored will also change
to the same tab index color.

Changing a cell to the same tab index color as it's value
 

Hi CRayF,

CRayF wrote:
I changed some of my other module code to pick up a number on this
Worksheet to use for the tab color index. These references are in
cells O6 through O30. It would be nice so that the "O" cell where
number is stored will also change to the same tab index color.

You may want to use the built-in Worksheet_Change event to handle this.
That way, if you ever change the value of a cell in the range O6:O30, the
color will automatically refresh. To do this, right-click the worksheet tab
and select View Code. Then paste the following code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngInt As Range
Dim c As Range

Set rngInt = Application.Intersect(Target, Range("O6:O30"))

If Not rngInt Is Nothing Then
For Each c In rngInt.Cells
c.Interior.ColorIndex = c.Value
Next c
End If

Set rngInt = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Changing a cell to the same tab index color as it's value
 

Worked like a champ... thanks

"Jake Marx" wrote:

Hi CRayF,

CRayF wrote:
I changed some of my other module code to pick up a number on this
Worksheet to use for the tab color index. These references are in
cells O6 through O30. It would be nice so that the "O" cell where
number is stored will also change to the same tab index color.

You may want to use the built-in Worksheet_Change event to handle this.
That way, if you ever change the value of a cell in the range O6:O30, the
color will automatically refresh. To do this, right-click the worksheet tab
and select View Code. Then paste the following code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngInt As Range
Dim c As Range

Set rngInt = Application.Intersect(Target, Range("O6:O30"))

If Not rngInt Is Nothing Then
For Each c In rngInt.Cells
c.Interior.ColorIndex = c.Value
Next c
End If

Set rngInt = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]