Hello,

I had a question answered , and code supplied. Works great. Now when I put 1
in a cell to change the background to black, the font needs to be changed to
white so I can see it, and if it's any other color than black keep the font
black.

I have something like

If valuess(i)=1 then
Font.ColorIndex=2 (white)
Interior.ColorIndex=1 (black)
Else
Font.ColorIndex=1 (black)
Interior.ColorIndex=valuess(i)
End If

I need to incorporate this into the code that was supplied. Thanks for any
help.



Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim CellVal As String

If Target.Cells.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("F1:F100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Set T = Target
codes = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
valuess = Array(1, 2, 3, 6, 10, 16, 17, 23, 38, 46)
v = T.Value
For i = 0 To 9
If v = codes(i) Then
Application.EnableEvents = False
T.EntireRow.Resize(1, 5).Interior.ColorIndex = valuess(i)
Application.EnableEvents = True
Exit Sub
End If
Next
End If

End Sub

Hi Brian,

Might I suggest that you're over-complicating this? Does Conditional
Formatting get you the same result?

Greg

On Apr 2, 10:18*am, "Brian" wrote:
Hello,

I had a question answered , and code supplied. Works great. Now when I put 1
in a cell to change the background to black, the font needs to be changed to
white so I can see it, and if it's any other color than black keep the font
black.

I have something like

If valuess(i)=1 then
* *Font.ColorIndex=2 (white)
* *Interior.ColorIndex=1 (black)
Else
* *Font.ColorIndex=1 (black)
* *Interior.ColorIndex=valuess(i)
End If

I need to incorporate this into the code that was supplied. Thanks for any
help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim CellVal As String

If Target.Cells.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("F1:F100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
* * Set T = Target
* * codes = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
* * valuess = Array(1, 2, 3, 6, 10, 16, 17, 23, 38, 46)
* * v = T.Value
* * For i = 0 To 9
* * * * If v = codes(i) Then
* * * * Application.EnableEvents = False
* * * * T.EntireRow.Resize(1, 5).Interior.ColorIndex = valuess(i)
* * * * Application.EnableEvents = True
* * * * Exit Sub
* * * * End If
* * Next
End If

End Sub

Hi Greg,

Thanks, you're right. That's much simpler

Brian

"Greg Glynn" wrote in message
...
Hi Brian,

Might I suggest that you're over-complicating this? Does Conditional
Formatting get you the same result?

Greg

On Apr 2, 10:18 am, "Brian" wrote:
Hello,

I had a question answered , and code supplied. Works great. Now when I put
1
in a cell to change the background to black, the font needs to be changed
to
white so I can see it, and if it's any other color than black keep the
font
black.

I have something like

If valuess(i)=1 then
Font.ColorIndex=2 (white)
Interior.ColorIndex=1 (black)
Else
Font.ColorIndex=1 (black)
Interior.ColorIndex=valuess(i)
End If

I need to incorporate this into the code that was supplied. Thanks for any
help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WatchRange As Range
Dim CellVal As String

If Target.Cells.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("F1:F100") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Set T = Target
codes = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
valuess = Array(1, 2, 3, 6, 10, 16, 17, 23, 38, 46)
v = T.Value
For i = 0 To 9
If v = codes(i) Then
Application.EnableEvents = False
T.EntireRow.Resize(1, 5).Interior.ColorIndex = valuess(i)
Application.EnableEvents = True
Exit Sub
End If
Next
End If

End Sub